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    gt  0  ORDER BY Employee ID

User · Answer

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  gt  0

User · Answer

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 gt    PageNumber-1   PageSize AND RowNumber lt    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 gt    PageNumber-1   PageSize AND RowNumber lt    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

User · Answer

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

User · Answer

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  gt  1  SELECT object id FROM  SELECT object id           ROW NUMBER   OVER   ORDER BY object id  RN     FROM sys objects  T WHERE RN  gt  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

User · Answer

SELECT  employee id FROM              SELECT  employee id  ROW NUMBER   OVER  ORDER BY employee id  AS rn         FROM    V EMPLOYEE           q WHERE   rn  gt  0 ORDER BY         Employee ID   Note that this filter is redundant  ROW NUMBER   starts from 1 and is always greater than 0

User · Answer

select salary from    select  Salary  ROW NUMBER   over  order by Salary desc  rn from Employee     t where t rn   2

User · Answer

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  gt  0

User · Answer

WITH MyCte AS        select         employee id         RowNum   row number   OVER  order by employee id      from V EMPLOYEE    SELECT  employee id FROM    MyCte WHERE   RowNum  gt  0 ORDER BY employee id

User · Answer

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  gt  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  gt  1

User · Answer

Select   from        Select ROW NUMBER   OVER   order by Id  as  Row Number          from tbl Contact Us   as tbl Where tbl Row Number   5

[sql] SQL Row_Number() function in Where Clause

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to analytic-functions