over - Función SQL Row_Number() en Where Clause

row_number() over(partition by

Question

Encontré una pregunta respondida con la función Row_Number() en la cláusula where. Cuando intenté una consulta, recibí el siguiente error:

Las funciones "Msg 4108, Nivel 15, Estado 1, Ventana 1 en ventana solo pueden aparecer en las cláusulas SELECT u ORDER BY".

Aquí está la consulta que probé. Si alguien sabe cómo resolver esto, házmelo saber.

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

Answer 1

Creo que quieres algo como esto:

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

Answer 2

En respuesta a los comentarios sobre la respuesta de rexem, con respecto a si una vista en línea o CTE sería más rápida, reformulé las consultas para usar una tabla I, y todos, tenían disponible: 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

Los planes de consulta producidos fueron exactamente iguales. Me gustaría esperar que en todos los casos, el optimizador de consultas presente el mismo plan, al menos en el reemplazo simple de CTE con vista en línea o viceversa.

Por supuesto, pruebe sus propias consultas en su propio sistema para ver si hay alguna diferencia.

Además, row_number() en la cláusula where es un error común en las respuestas dadas en Desbordamiento de pila. Logicaly row_number() no está disponible hasta que se procesa la cláusula de selección. La gente lo olvida y cuando responden sin probar la respuesta, a veces la respuesta es incorrecta. (Un cargo del que he sido culpable).

Answer 3

Para evitar este problema, ajuste su instrucción de selección en un CTE, y luego puede consultar contra el CTE y usar los resultados de la función de ventana en la cláusula where.

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

Answer 4

Usando 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

Usando la vista en línea / alternativa equivalente no CTE:

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

Answer 5

basado en la respuesta de OP a la pregunta:

Por favor mira este enlace. Tiene una solución diferente, que parece funcionar para la persona que hizo la pregunta. Estoy tratando de encontrar una solución como esta.

Consulta paginada utilizando ordenación en columnas diferentes usando ROW_NUMBER () OVER () en SQL Server 2005

~ Joseph

"método 1" es como la consulta de OP de la pregunta vinculada, y "método 2" es como la consulta de la respuesta seleccionada. Había que mirar el código vinculado en esta answer para ver qué sucedía realmente, ya que el código en la respuesta seleccionada se había modificado para que funcionara. Prueba esto:

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

SALIDA:

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)

Answer 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

Answer 7

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

Tenga en cuenta que este filtro es redundante: ROW_NUMBER() comienza desde 1 y siempre es mayor que 0 .

Answer 8

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