Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get consecutive record number in two select statement Expand / Collapse
Author
Message
Posted Monday, August 2, 2010 11:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 14, 2012 10:01 AM
Points: 5, Visits: 10
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


Post #962594
Posted Tuesday, August 3, 2010 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 6,842, Visits: 13,372
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #962670
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse