CODEWITHSUNDEEP
mysqlsql
majid_shoorabi
majid_shoorabi

Reputation: 129

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.

Upvotes: 0

Views: 880

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do this with group by and a having clause:

select ticket_Id, max(ticketstatus_Id)
from ticketstatuses
group by ticket_id
having max(ticketstatus_Id) = max(case when status_id = 2 then ticketstatus_Id end);

It would be interesting to know if this has better performance than the row_number() version. However, for performance, this is probably best:

select ts.*
from ticketstatuses ts
where ts.ticketstatus_Id = (select max(ts2.ticketstatus_Id)
                            from ticketstatus ts2
                            where ts2.ticket_id = ts.ticket_id
                           ) and
      ts.status_id = 2;

This can take advantage of an index on (ticket_id, ticketstatus_id).

Upvotes: 1

Manish Sharma
Manish Sharma

Reputation: 249

SELECT * from TABLENAME where status_Id = '2' ORDER BY ticketstatus_Id DESC LIMIT 1;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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.

Upvotes: 1

Devsi Odedra
Devsi Odedra

Reputation: 5322

All records with status_Id= '2'

Select * from TABLENAME where status_Id = '2'

Last record with status_Id= '2'

Select * from TABLENAME where status_Id = '2' order by  ticketstatus_Id  desc limit 1

Upvotes: 0

Related Questions