how to filter data using qualify row_number in sql server [closed]

Question

I am trying to use QUALIFY to filter the data without seccess (in SQL SERVER) also, I do not want to create a table with the row_number in the SELECT statement and then use WHERE clause.

select *
from [dbo].[DWH_TicketThread_View]
where 1=1
and threadtype in (313,347,349,385,392,417)
and TicketId = 651353
qualify row_number() over(partition by ticketid order by updatedate desc) = 1

Does this answer your question? QUALIFY-Like Function in SQL Server — Equinox
This question is completely clear to me, and I don't understand why it has been closed with the given reason above. — Tim Biegeleisen
I couldn't agree more. the answer is well written and understood even to a basic-understanding audience. Tim thank you for solving my question. — Eyal Marom

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2020-12-10T08:48:34

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;

how to filter data using qualify row_number in sql server [closed]

1 Answers