Row_Number() with union query

Question

I have an sql query that returns rows from two tables with same column names. I need a row number of all records in a complete result set.

Following is the query

select Id, VersionNumber from documents where id=5  Union all   select Id, VersionNumber from versions where id=5 order by VersionNumber desc 

How should it be done?

EDIT: And what if I only need to return a row_num where VersionNumber is lets say 5.

Answer 1

Try this:

SELECT *, ROW_NUMBER() OVER(ORDER BY Id) ROW_NUM   FROM (         select Id, VersionNumber from documents where id=5          Union all           select Id, VersionNumber from versions where id=5        ) a order by VersionNumber desc 

TO filter by version number 5 use:

SELECT *  FROM   (SELECT *,                 Row_number() OVER(ORDER BY versionnumber DESC, id) row_num          FROM   (SELECT id,                         versionnumber                  FROM   documents                  WHERE  id = 5                  UNION ALL                  SELECT id,                         versionnumber                  FROM   versions                  WHERE  id = 5) a) b  WHERE  version = 5  

Answer 2

A slightly better way would be :-

with resultset1 as(    select Id, VersionNumber from documents where id=5     Union all      select Id, VersionNumber from versions where id=5 ), resultset2 as(    select Id, VersionNumber, ROW_NUMBER() OVER(ORDER BY Id) ROW_NUM    from resultset1 ) select * from resultset2 WHERE VersionNumber = 5