Search code examples
sqlunionrow-numberunion-all

Row_Number() continue in Union all query

How can i get the incremental unique number if i have 3 tables?

for example:

  • 1st query row_number result = 1,2,3
  • 2nd query row_number result = 4,5,6
  • 3rd query row_number result = 7,8,9

I tried the below query but from that i can get the incremental upto 2nd table.

SELECT ROW_NUMBER() OVER(ORDER BY filename) AS SrNo,fileName FROM Tab1
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY filename) + (SELECT COUNT(*) FROM tab1) AS 
SrNo, filename FROM Tab2
UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY filename) + (SELECT COUNT(*) FROM tab2) AS 
SrNo, filename FROM Tab3

each table has 3 records and i want to get the result of row_number as 1,2,3,4,5,6,7,8,9

Solution

  • Use a subquery:

    SELECT ROW_NUMBER() OVER (ORDER BY which, filename) as SrNo, filename
FROM (SELECT 1 as which, fileName FROM Tab1
      UNION ALL
      SELECT 2 as which, filename FROM Tab2
      UNION ALL
      SELECT 3 as which, filename FROM Tab3
     ) ttt;