Row_Number () continue in Union all query

Question

How can I get an incremental unique number if I have 3 tables?

eg:

• 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 following query, but from this I can get incremental to the 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

Answer 1

Use 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;