sql server - example - Seleccione stmt con ROW_NUMBER() OVER(PARTITION BY...) en Where clause

sql row number over

Question

Debo estar haciendo algo mal. Estoy intentando usar la función row_number para seleccionar solo los valores que tienen un Rownum de 1 , para que siempre obtenga el último índice . Sin embargo rownum no se reconoce en la cláusula where.

SELECT fs.docu_id as docID
, fs.field_3 AS ProductNo
, fs.field_4 AS [Status]
, fs.field_5 AS [Index]
,pd.[doku_nr] AS docIDshort
, ROW_NUMBER() OVER(PARTITION BY fs.field_3 ORDER BY fs.field_5 Desc) AS rownum
FROM [table1] fs
JOIN [table2] pd
ON fs.docu_id=pd.docu_id
AND fs.field_4 = ''valid''

¿Qué me estoy perdiendo? ¿Debo crear una stmt de selección diferente?

Gracias.

Answer 1

No puede usar funciones de ventana en la cláusula Where (solo en SELECT u ORDER BY ).

Pero podría usar un CTE en su lugar:

WITH CTE
AS (SELECT fs.docu_id AS docID,
fs.field_3 AS ProductNo,
fs.field_4 AS [Status],
fs.field_5 AS [Index],
pd.[doku_nr] AS docIDshort,
Row_number() OVER(
Partition BY fs.field_3
ORDER BY fs.field_5 DESC) AS rownum
FROM [table1] fs
JOIN [table2] pd
ON fs.docu_id = pd.docu_id
AND fs.field_4 = ''valid'')
SELECT docid, productno, status, [index], docIDshort
FROM CTE
WHERE rownum = 1