I have a table that store ticket and statue relation
ticketstatus_Id ticket_Id status_Id =================================== 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 * 6 3 1 7 4 1 8 3 2 *
I want to select rows that last status_Id equal 2 ,Rows are marked in table. I think I have to use GROUP BY on column ticket_Id but it return with first status_Id.
Advertisement
Answer
This problem is a good candidate for ROW_NUMBER
:
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ticket_Id ORDER BY ticketstatus_Id DESC) rn FROM yourTable ) SELECT ticketstatus_Id, ticket_Id, status_Id FROM cte WHERE rn = 1 AND status_Id = 2;
The above logic finds all latest rows for each ticket_Id
, as ordered by the ticketstatus_Id
, whose status_Id
values also happens to be 2.