Sql Server Alias name in Row_Number function -

select tmp.id, tmp.portfolio,    row_number() over(order tmp.id desc) rownum     (select r.portfolio, r.id    research r     r.created_by = 'adam cohen'  ) tmp  rownum between 5 , 10; 

i not able refer rownum in condition, shows invalid column name 'rownum'. kindly me use right syntax result.

edit - changed requirement

select *  (       select id, portfolio,       case when l.posted_on null            convert(varchar(40),l.created_on,120)            else convert(varchar(40),l.posted_on,120)       end sort_by,       row_number() over(order sort_by desc) rownum       research       created_by = 'adam cohen'  ) x  x.rownum between 5 , 10  

i tried include row_number function above got sort_by invalid column.

you need nest row_number in derived table:

select tmp.id, tmp.portfolio, tmp.rownum    (select r.portfolio, r.id,       row_number() over(order r.id desc) rownum     research r     r.created_by = 'adam cohen'  ) tmp  rownum between 5 , 10;