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.
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