Skip to content
Advertisement

Select the last record in MySQL in WHERE condition

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.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement