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

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

Answer

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;