Search code examples
sqlsql-serverrow-number

how to filter data using qualify row_number in sql server

Has SQL SERVER come up with a new alternative to QUALIFY?

select *
from my_table
where 1=1
and data_type in (313,347,349,385,392,417)
and data_id = 651353
qualify row_number() over(partition by data_id order by data_update_datedesc) = 1
Solution

  • SQL Server does not support QUALIFY which appears in other databases such as Teradata. Here is one way to write your query in a similar way, without a formal subquery, using a TOP 1 WITH TIES trick:

    SELECT TOP 1 WITH TIES *
FROM [dbo].[DWH_TicketThread_View]
WHERE threadtype IN (313, 347, 349, 385, 392, 417) AND TicketId = 651353
ORDER BY ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY updatedate DESC);

    But the above is actually not that performant, so more typically we would in fact use a subquery here:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ticketid ORDER BY updatedate DESC) rn
    FROM [dbo].[DWH_TicketThread_View]
    WHERE threadtype IN (313, 347, 349, 385, 392, 417) AND TicketId = 651353
)

SELECT *
FROM cte
WHERE rn = 1;