How to get consecutive record number in two select statement

  • Hi,

    select row_number() over(order by b.inv_no) as sl_no,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 row_number() over(order by b.inv_no) as sl_no,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)

    Above query will return set of values. Now I want to get consecutive record number. How can I achieve

  • 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]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply