Home Forums SQL Server 2005 SQL Server Express How to get consecutive record number in two select statement RE: How to get consecutive record number in two select statement

  • You could wrap your UNION statment in a subquery or CTE. This way you could save one sorting operation by moving the ROW_NUMBER() function to the outer SELECT.

    At a first glance it seems like the UNION statment can be eliminated by using LEFT OUTER JOINS (btw: you should change your syntax to use JOIN instead of cross join and WHERE conditions...). To verify it I would need table definition and smoe sample data to test against.

    ;

    WITH cte AS

    (

    SELECT 1 AS intermed,b.fyear_code,b.inv_no,b.inv_date,b.service_nature,b.inv_total,c.bal_amount,b.client_code,b.amt_received FROM fn_receipt a, trn_invoice_history b, mst_expenditureinvoice c WHERE a.client_id=b.client_code AND a.client_name=b.client_name AND a.location=b.client_location AND a.receiptno=35 AND b.fully_recived NOT IN ('Y') AND b.amt_balance>0 AND (c.fyear_code=b.fyear_code AND b.inv_no=c.inv_no)

    UNION

    SELECT 2 AS intermed,b.fyear_code,b.inv_no,b.inv_date,b.service_nature,b.inv_total,c.bal_amount,b.client_code,b.amt_received FROM fn_receipt a, trn_invoice_history b, mst_invoice c WHERE a.client_id=b.client_code AND a.client_name=b.client_name AND a.location=b.client_location AND a.receiptno=35 AND b.fully_recived NOT IN ('Y') AND b.amt_balance>0 AND (c.fyear_code=b.fyear_code AND b.inv_no=c.inv_no)

    )

    SELECT

    ROW_NUMBER() OVER(ORDER BY intermed,b.inv_no) AS total_no,

    ROW_NUMBER() OVER(partitioned BY intermed ORDER BY b.inv_no) AS sl_no,

    fyear_code,

    inv_no,

    inv_date,

    service_nature,

    inv_total,

    bal_amount,

    client_code,

    amt_received

    FROM cte



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]