Row_number selecting from a complex select statement

Hi,

Code Snippet

This is difficult to explain in words, but the following code outlines what I am trying to do:

with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)

select * from myTableWithRowNum

Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?

The following query might help you,

Code Snippet

;with UnionResult(myvalue,insertdate)

as

(

select table1Id As myValue,insertdate from myTable1

union

select table2Id As myValue,insertdate from myTable2

),

OrderedResult(myValue,Row)

as

(

select myValue, row_number() over (order by insertdate desc)

from UnionResult

)

select * from OrderedResult

|||

I m not sure I understand your requirment correctly,anyhow your query throws error,

Try the following

Code Snippet

;with myTableWithRowNum as

(

select 'row' = row_number() over (order by insertdate desc), myValue

from

(

select insertdate,table1Id As myValue from myTable1

union

select insertdate,table2Id As myValue from myTable2

) as temp

)

select * from myTableWithRowNum

|||Thanks that's exactly what I'm looking for.
sql