Hi All, I found one question answered with Row_Number() function in 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. Thanks in Advance.
SELECT employee_id
FROM V_EMPLOYEE
WHERE row_number() OVER ( ORDER BY employee_id ) > 0
ORDER BY Employee_ID
~Joseph
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)
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
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.
~Joseph
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.
~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)