How to get consecutive record number in two select statement

[visweswaran28]

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

[MeSampath]

Hi,

Row_number will give the consecutive numbers. But in this case, Union will always merge the results, so you will not get the sequential numbers.

To get this done, you can modify the query with combining the tables such as fn_receipt a, trn_invoice_history b, mst_expenditureinvoice c and mst_invoice in a single query (without union operator) something like below;

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, mst_invoice d 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) and (d.fyear_code=b.fyear_code and b.inv_no=d.inv_no)

The above query may not perfect (i believe you may need to use Outer joins). You can try this way.

Note:
Always use Code-tags while posting the queries.

Thank you.

[tesuji]

Hello

I am not sure whether even and odd series of row numbers could help:

select -1+2*row_number() over (order by ID1 ASC) as rownumber, 'First Set' as whichSet, colA as "Data" from rowTest
union
select 2*row_number() over (order by ID2 DESC) as rownumber, 'Second Set' as whichSet, colB as "Data" from rowTest
order by rownumber;
/* result:
rownumber  whichSet    Data
---------------------------
1          First Set   51
2          Second Set  27
3          First Set   10
4          Second Set  25
5          First Set   20
6          Second Set  26
*/

In first select -1+2*row_number() calculates the odd series: 1, 3, 5 etc. 2*row_number() in 2nd select makes series 2, 4, 6 etc. Where the order by rownumber put them into series 1, 2, 3 etc.

Is that what you mean by consecutive record number?

(Note: If I change -1+2*row_number() to 2*row_number()-1 Sybase DB says there is illegal char '-' after (), I don't why. But -1+2*row_number() works fine.)

Addition: Instead of calculating even and odd series also this is possible by adding 1+count(*) of first set to row_number() of second set:

/* result:
rownumber  whichSet    Data
---------------------------
1          First Set   51
2          First Set   10
3          First Set   20
4          Second Set  27
5          Second Set  25
6          Second Set  26*/

-- tesu