Where 子句中的 SQLRow_Number()函数

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.

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

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

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

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.)

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)

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

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

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

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:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE/ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP
12. 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.