Filter with Row_Number

Sreekanth Reddy

7y773

Why below is not possible ?

https://rextester.com/IPXPG10201

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
Accepted 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 Kumar7y ago
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 Reddy7y ago
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 Kumar7y ago
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