SQL Row_Number() function in Where Clause

Question

I found one question answered with the Row_Number() function in the where clause. When I tried one query, I was getting the following error:

"Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses."

Here is the query I tried. If somebody knows how to solve this, please let me know.

SELECT employee_id 
FROM V_EMPLOYEE 
WHERE row_number() OVER ( ORDER BY employee_id ) > 0 
ORDER BY Employee_ID

`ROW_NUMBER() OVER (ORDER BY employee_id) > 0` will always evaluate to `TRUE` — Quassnoi, Sep 23 '09 at 16:37
Yea, thats right. I am not worried about the condition, which I can change any time. I want the query to work first, then thinking of keeping the rownumber between 500 and 800... thanks — , Sep 23 '09 at 17:31
@rexem - I am not an expert in SQL Server. I'm trying to help a team in a big project where they are facing lots of issues with performance. They are using UDFs and CTEs. In one of the table, they have just 5000 records, and if 5 users accessing a search, it take more than a minute to retrieve. Some time, it fails and time out. So, I'm trying to avoid CTE and UDFs and trying to come up with a straight forward SQL query which can solve the performance issues. — , Sep 23 '09 at 17:36
Hi all, Please see the link I've posted below which answers using row_number() in a different way. Can somebody compare my initial query with the one in the link? Appreciate the help.. — , Sep 23 '09 at 17:37
[Why no windowed functions in where clauses? - example why it is not possible](https://stackoverflow.com/a/33522288/5070879) — Lukasz Szozda, Nov 17 '19 at 17:05

Scott Ivey · Answer 1 · 2009-09-23T16:15:00.843

113

To get around this issue, wrap your select statement in a CTE, and then you can query against the CTE and use the windowed function's results in the where clause.

WITH MyCte AS 
(
    select   employee_id,
             RowNum = row_number() OVER ( order by employee_id )
    from     V_EMPLOYEE 
    ORDER BY Employee_ID
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0

edited Sep 23 '09 at 16:15

answered Sep 23 '09 at 16:04

Scott Ivey

40,768
21
80
118

8

I'm trying to avoid CTE. Thats the worse case I'm looking for. thanks – Sep 23 '09 at 17:32
4

It may run faster if you use a subquery instead of a CTE. I've seen better performance by a factor of 1.5 in some cases – Brian Webster Apr 13 '11 at 04:33
3

There should be also TOP in the CTE SELECT otherwise SQL 2008 Server will not execute the query because of ORDER BY (which is unsupported unless TOP is used) – Muflix Jun 30 '15 at 15:01
2

I am using SQL2005 ( ugh ) -- I can avoid the use of "TOP", by dropping the "ORDER BY" after the FROM. It is redundant with the ( Order By ) after the OVER anyhow. – Joe B Mar 20 '17 at 16:43
I was wishing there is a way to use `ROW_NUMBER()` in `WHERE` clause without CTE :( – Jalal Jan 06 '19 at 15:50
I don't think you need the second ORDER BY clause – andyabel Aug 26 '21 at 15:15

score 72 · Answer 2 · answered Sep 23 '09 at 16:04

72

SELECT  employee_id
FROM    (
        SELECT  employee_id, ROW_NUMBER() OVER (ORDER BY employee_id) AS rn
        FROM    V_EMPLOYEE
        ) q
WHERE   rn > 0
ORDER BY
        Employee_ID

Note that this filter is redundant: ROW_NUMBER() starts from 1 and is always greater than 0.

answered Sep 23 '09 at 16:04

Quassnoi

413,100
91
616
614

@Quassnoi Thanks! Curiosity: why is there `q`? What does it mean? – DavideChicco.it Feb 02 '16 at 20:32
2

@DavideChicco.it: in SQL Server, derived tables require an alias (I should have written `AS q` instead, but this would work either). – Quassnoi Feb 02 '16 at 20:57
2

Readability is a focus I have when naming aliases. You could write rn as RowNumber and q as DerivedTable and the where clause as where DerivedTable.RowNumber > 0. In my opinion this will be far less confusing in 6 months time when the code isn't fresh in your mind. – Edward Comeau Oct 05 '16 at 09:06
3

@EdwardComeau: `rn` is kinda universally accepted acronym for row number these days. Try typing "row_number over as..." into google search string and see what it suggests you. – Quassnoi Oct 05 '16 at 10:31
3

@Quassnoi, readability is key to good coding and the cognitive effort of translating rn (or other abbreviated aliases) adds up for yourself and the people maintaining your code. NB, Microsoft first hit, SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,... I have also not come across rn before so your mileage in "universal" may vary. – Edward Comeau Oct 05 '16 at 11:07
1

@Quassnoi, and second hit, SO article - http://stackoverflow.com/questions/961007/how-do-i-use-row-number several variations and not rn ;-) – Edward Comeau Oct 05 '16 at 11:09
@EdwardComeau: whatever floats your boat! – Quassnoi Oct 05 '16 at 12:19
If a person wants to just order by row number, then after OVER, you can have `ORDER BY (SELECT 100)` and can skip out ORDER BY BlahID at the end. – barlop Apr 26 '18 at 13:19

score 36 · Answer 3 · edited Mar 19 '12 at 16:03

36

Select * from 
(
    Select ROW_NUMBER() OVER ( order by Id) as 'Row_Number', * 
    from tbl_Contact_Us
) as tbl
Where tbl.Row_Number = 5

edited Mar 19 '12 at 16:03

tobias86

4,979
1
21
30

answered Mar 19 '12 at 05:54

swa

361
3
2

score 25 · Answer 4 · edited Dec 03 '15 at 16:50

25

I think you want something like this:

SELECT employee_id 
FROM  (SELECT employee_id, row_number() 
       OVER (order by employee_id) AS 'rownumber' 
       FROM V_EMPLOYEE) TableExpressionsMustHaveAnAliasForDumbReasons
WHERE rownumber > 0

edited Dec 03 '15 at 16:50

McKay

12,334
7
53
76

answered Sep 23 '09 at 16:05

Matthew Jones

25,644
17
102
155

4

Create an alias for the table if the above query does not work for you. Modify second last line as `From V_EMPLOYEE) A` that is add A as alias. – TheTechGuy Nov 05 '14 at 05:46

score 12 · Answer 5 · answered Aug 13 '19 at 21:31

I feel like all the answers showing use of a CTE or Sub Query are sufficient fixes for this, but I don't see anyone getting to the heart of why OP has a problem. The reason why what OP suggested doesn't work is due to logical query processing order here:

FROM

ON

JOIN

WHERE

GROUP BY

WITH CUBE/ROLLUP

HAVING

SELECT

DISTINCT

ORDER BY

TOP

OFFSET/FETCH

I believe this contributes to the answer greatly, because it explains why issues like this one occur. WHERE is always processed before SELECT making a CTE or Sub Query necessary for many functions. You will see this a lot in SQL Server.

score 10 · Answer 6 · answered Sep 23 '09 at 18:15

10

In response to comments on rexem's answer, with respect to whether a an inline view or CTE would be faster I recast the queries to use a table I, and everyone, had available: sys.objects.

WITH object_rows AS (
    SELECT object_id, 
        ROW_NUMBER() OVER ( ORDER BY object_id) RN
    FROM sys.objects)
SELECT object_id
FROM object_rows
WHERE RN > 1

SELECT object_id
FROM (SELECT object_id, 
        ROW_NUMBER() OVER ( ORDER BY object_id) RN
    FROM sys.objects) T
WHERE RN > 1

The query plans produced were exactly the same. I would expect in all cases, the query optimizer would come up with the same plan, at least in simple replacement of CTE with inline view or vice versa.

Of course, try your own queries on your own system to see if there is a difference.

Also, row_number() in the where clause is a common error in answers given on Stack Overflow. Logicaly row_number() is not available until the select clause is processed. People forget that and when they answer without testing the answer, the answer is sometimes wrong. (A charge I have myself been guilty of.)

answered Sep 23 '09 at 18:15

Shannon Severance

18,025
3
46
67

1

Thx Shannon. What version of SQL Server were you using? – OMG Ponies Sep 23 '09 at 18:48
1

So that means, the answer provided in that link is wrong? But, the person who posted the question agreed that its working.. Surprising.. :-) – Sep 23 '09 at 20:45
2

@Joseph, but if you look at another answer posted by the OP in the linked question, you will see that he link's to a version of the code that is not the same as in the accepted answer. I don't know why he accepted the answer, even though it would not run as entered. Maybe it was edited at some point after being accepted, maybe it was enough to get him going, even without being totally correct. – Shannon Severance Sep 24 '09 at 02:48
1

@Rexem: Both SQL Server 2005 & SQL Server 2008. Earlier versions do not support CTEs or ROW_NUMBER() – Shannon Severance Sep 24 '09 at 02:51

score 6 · Answer 7 · edited Oct 05 '16 at 08:59

6

WITH MyCte AS 
(
    select 
       employee_id,
       RowNum = row_number() OVER (order by employee_id)
    from V_EMPLOYEE 
)
SELECT  employee_id
FROM    MyCte
WHERE   RowNum > 0
ORDER BY employee_id

edited Oct 05 '16 at 08:59

LukStorms

28,916
5
31
45

answered Nov 16 '15 at 11:42

sumit

99
1
1

score 4 · Answer 8 · answered Sep 23 '09 at 16:29

4

Using CTE (SQL Server 2005+):

WITH employee_rows AS (
  SELECT t.employee_id,
         ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
    FROM V_EMPLOYEE t)
SELECT er.employee_id
  FROM employee_rows er
 WHERE er.rownum > 1

Using Inline view/Non-CTE Equivalent Alternative:

SELECT er.employee_id
  FROM (SELECT t.employee_id,
               ROW_NUMBER() OVER ( ORDER BY t.employee_id ) 'rownum'
          FROM V_EMPLOYEE t) er
 WHERE er.rownum > 1

answered Sep 23 '09 at 16:29

OMG Ponies

325,700
82
523
502

1

Which one better in performance? Using CTE or subquery? thanks – Sep 23 '09 at 17:32
2

See Shannon's answer - in his test they are equal. – OMG Ponies Sep 23 '09 at 18:49
6

No, it's not faster. In `SQL Server`, `CTE`'s and inline views are the same thing and have same performance. When non-deterministic functions are used in a `CTE`, its reevaluated on each call. One has to use dirty tricks to force materializationof a `CTE`. See these articles in my blog: http://explainextended.com/2009/07/28/sql-server-random-records-avoiding-cte-reevaluation/ http://explainextended.com/2009/05/28/generating-xml-in-subqueries/ – Quassnoi Sep 23 '09 at 21:17

score 2 · Answer 9 · edited May 23 '17 at 11:54

based on OP's answer to question:

Please see this link. Its having a different solution, which looks working for the person who asked the question. I'm trying to figure out a solution like this.

Paginated query using sorting on different columns using ROW_NUMBER() OVER () in SQL Server 2005

~Joseph

"method 1" is like the OP's query from the linked question, and "method 2" is like the query from the selected answer. You had to look at the code linked in this answer to see what was really going on, since the code in the selected answer was modified to make it work. Try this:

DECLARE @YourTable table (RowID int not null primary key identity, Value1 int, Value2 int, value3 int)
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,1,1)
INSERT INTO @YourTable VALUES (1,1,2)
INSERT INTO @YourTable VALUES (1,1,3)
INSERT INTO @YourTable VALUES (1,2,1)
INSERT INTO @YourTable VALUES (1,2,2)
INSERT INTO @YourTable VALUES (1,2,3)
INSERT INTO @YourTable VALUES (1,3,1)
INSERT INTO @YourTable VALUES (1,3,2)
INSERT INTO @YourTable VALUES (1,3,3)
INSERT INTO @YourTable VALUES (2,1,1)
INSERT INTO @YourTable VALUES (2,1,2)
INSERT INTO @YourTable VALUES (2,1,3)
INSERT INTO @YourTable VALUES (2,2,1)
INSERT INTO @YourTable VALUES (2,2,2)
INSERT INTO @YourTable VALUES (2,2,3)
INSERT INTO @YourTable VALUES (2,3,1)
INSERT INTO @YourTable VALUES (2,3,2)
INSERT INTO @YourTable VALUES (2,3,3)
INSERT INTO @YourTable VALUES (3,1,1)
INSERT INTO @YourTable VALUES (3,1,2)
INSERT INTO @YourTable VALUES (3,1,3)
INSERT INTO @YourTable VALUES (3,2,1)
INSERT INTO @YourTable VALUES (3,2,2)
INSERT INTO @YourTable VALUES (3,2,3)
INSERT INTO @YourTable VALUES (3,3,1)
INSERT INTO @YourTable VALUES (3,3,2)
INSERT INTO @YourTable VALUES (3,3,3)
SET NOCOUNT OFF

DECLARE @PageNumber     int
DECLARE @PageSize       int
DECLARE @SortBy         int

SET @PageNumber=3
SET @PageSize=5
SET @SortBy=1


--SELECT * FROM @YourTable

--Method 1
;WITH PaginatedYourTable AS (
SELECT
    RowID,Value1,Value2,Value3
        ,CASE @SortBy
             WHEN  1 THEN ROW_NUMBER() OVER (ORDER BY Value1 ASC)
             WHEN  2 THEN ROW_NUMBER() OVER (ORDER BY Value2 ASC)
             WHEN  3 THEN ROW_NUMBER() OVER (ORDER BY Value3 ASC)
             WHEN -1 THEN ROW_NUMBER() OVER (ORDER BY Value1 DESC)
             WHEN -2 THEN ROW_NUMBER() OVER (ORDER BY Value2 DESC)
             WHEN -3 THEN ROW_NUMBER() OVER (ORDER BY Value3 DESC)
         END AS RowNumber
    FROM @YourTable
    --WHERE
)
SELECT
    RowID,Value1,Value2,Value3,RowNumber
        ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
    FROM PaginatedYourTable
    WHERE RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
    ORDER BY RowNumber



--------------------------------------------
--Method 2
;WITH PaginatedYourTable AS (
SELECT
    RowID,Value1,Value2,Value3
        ,ROW_NUMBER() OVER
         (
             ORDER BY
                 CASE @SortBy
                     WHEN  1 THEN Value1
                     WHEN  2 THEN Value2
                     WHEN  3 THEN Value3
                 END ASC
                ,CASE @SortBy
                     WHEN -1 THEN Value1
                     WHEN -2 THEN Value2
                     WHEN -3 THEN Value3
                 END DESC
         ) RowNumber
    FROM @YourTable
    --WHERE  more conditions here
)
SELECT
    RowID,Value1,Value2,Value3,RowNumber
        ,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
    FROM PaginatedYourTable
    WHERE 
        RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
        --AND more conditions here
    ORDER BY
        CASE @SortBy
            WHEN  1 THEN Value1
            WHEN  2 THEN Value2
            WHEN  3 THEN Value3
        END ASC
       ,CASE @SortBy
            WHEN -1 THEN Value1
            WHEN -2 THEN Value2
            WHEN -3 THEN Value3
        END DESC

OUTPUT:

RowID  Value1 Value2 Value3 RowNumber  PageNumber  PageSize    SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10     2      1      1      10         3           5           1
11     2      1      2      11         3           5           1
12     2      1      3      12         3           5           1
13     2      2      1      13         3           5           1
14     2      2      2      14         3           5           1

(5 row(s) affected

RowID  Value1 Value2 Value3 RowNumber  PageNumber  PageSize    SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10     2      1      1      10         3           5           1
11     2      1      2      11         3           5           1
12     2      1      3      12         3           5           1
13     2      2      1      13         3           5           1
14     2      2      2      14         3           5           1

(5 row(s) affected)

fyi, when using _SET SHOWPLAN_ALL ON_ method 1 had a TotalSubtreeCost of 0.08424953, while method 2 was at 0.02627153. method 2 was over three times better. — KM., Sep 23 '09 at 18:36
@rexem, both method 1 and 2 use CTEs, the way they paginate and order rows is different. I'm not sure why this actual question is so different from the question that the OP links to (in the answer to this question by the OP), but my answer creates working code based on the link that the OP refers to — KM., Sep 23 '09 at 19:03
Thanks, I'm trying to compare the old post and this answers. [I don't know how to format this] Here is the answer provided by Tomalak. http://stackoverflow.com/questions/230058?sort=votes#sort-top Is this wrong? If he posted only half of the answer, how will I go ahead with his better performance way of doing my query? Please give me some more light to proceed.. thanks — , Sep 23 '09 at 20:52
@Joseph, the selected answer in the link you provide (http://stackoverflow.com/questions/230058?sort=votes#sort-top) differs from the working code that the person asking the question provides as working in their answer: http://stackoverflow.com/questions/230058/paginated-query-using-sorting-on-different-columns-using-rownumber-over-in/240108#240108 if you read that answer you will see a link to their code: http://pastebin.com/f26a4b403 and a link to their version of Tomalak's: http://pastebin.com/f4db89a8e in my answer I provide a working version of each version using table variables — KM., Sep 24 '09 at 13:13

score 0 · Answer 10 · edited Jan 17 '19 at 06:08

0

 select salary from (
 select  Salary, ROW_NUMBER() over (order by Salary desc) rn from Employee 
 ) t where t.rn = 2

edited Jan 17 '19 at 06:08

Vineeth Sai

3,389
7
23
34

answered Jan 17 '19 at 05:41

Aziz Khan

21

3

Welcome to Stack Overflow! While this code snippet may be the solution, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-‌code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Johan Jan 17 '19 at 07:46
Please add some context to the _code snippet_ for the benefit of the future readers. – undetected Selenium Jan 17 '19 at 09:25

SQL Row_Number() function in Where Clause

10 Answers10

Linked

Related