Ranking Functions: ROW_NUMBER

re: Ranking Functions: ROW_NUMBER

Craig Freedman — Mon, 17 Sep 2012 15:44:14 GMT

The WHERE clause is applied before any aggregation or ranking functions and, thus, cannot reference the results of these functions including ROW_NUMBER(). You can work around this limitation using subqueries or CTEs. For example:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T) Q
WHERE RowNumber <= 3;

WITH Q AS (SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T)
SELECT * FROM Q WHERE RowNumber <= 3;

HTH,
Craig

re: Ranking Functions: ROW_NUMBER

sadegh — Sat, 15 Sep 2012 06:47:19 GMT

why where clause doesn't work on rowNumber column???

re: Ranking Functions: ROW_NUMBER

Craig Freedman — Mon, 30 Apr 2012 15:44:43 GMT

Hi Naveen,

I'm not sure I follow your question. SeqNum is computed by the ROW_NUMBER() function and cannot be updated directly. Only by changing the values of Prcname and submitternode can you change the results generated by the ROW_NUMBER() function.

HTH,

Craig

re: Ranking Functions: ROW_NUMBER

Naveen — Sat, 28 Apr 2012 23:08:18 GMT

Please could you tell me how to use below code to update field seqnum

select*, -1+ROW_NUMBER() OVER (PARTITION BY Prcname ORDER BY submitternode) AS SeqNum FROM Test_nav1

Ranking Functions: RANK, DENSE_RANK, and NTILE

Craig Freedman's SQL Server Blog — Tue, 01 Apr 2008 00:14:57 GMT

In my previous post , I discussed the ROW_NUMBER ranking function which was introduced in SQL Server