Filter with Row_Number

Question

Why below is not possible ? https://rextester.com/IPXPG10201

Suraj Kumar · Answer

Try the following create table #tmp(roleid int ,comid varchar ) insert into #tmp values (1, 'A' ),(1, 'B' ),(2, 'C' ),(2, 'D' ),(1, 'A' ) select * from #tmp select roleid,comid, row_number() over(partition by comid order by roleid) as rno from #tmp -- How to get whose rno is > than 1 ? I tried like below. Why its not giving by my query ? select rno, * from ( select roleid,comid, row_number() over(partition by comid order by roleid) as rno from #tmp )a where rno > 1 drop table #tmp Check the domo here - https://rextester.com/OXVBBK85493

Suraj Kumar · Answer

It is because windows function are executed at the end after processing group by, having, where, etc. operations. At the time you were using rno with where condition but as I said it execute after where so it was giving error.

Sreekanth Reddy · Answer

Hi suraj, Why its not working with my query ? select roleid,comid, row_number() over(partition by comid order by roleid) as rno from #tmp where rno > 1 Please explain. I'm beginner in sql server. Thank you.

Suraj Kumar · Answer

Windowed functions can only appear in the SELECT or ORDER BY clauses. https://stackoverflow.com/questions/14111321/windowed-functions-can-only-appear-in-the-select-or-order-by-clauses