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;