CODEWITHSUNDEEP
mysqlduplicatesgreatest-n-per-group
Shanon
Shanon

Reputation: 11

Mysql select one of duplicate rows

I am trying to select the latest entry of the duplicate entries against the ticket_id in a mysql table , My current is something like this.

SELECT  * ,
        COUNT(*) AS cnt ,
        ticket_id
FROM    temp_tickets
GROUP BY ticket_id
ORDER BY id DESC

It gives the number of times a row is duplicated but i am able to select the latest one of those mulptiple rows

Let say i have 3 ticket_id's which got duplicated for 5 times so now i want to select the latest occurrence from all these 3 id's .

Lemme know if i have to be more specific.

Thanks

Upvotes: 1

Views: 4907

Answers (1)

Martin Smith
Martin Smith

Reputation: 452977

Here's one way (assuming "latest" means "greatest id")

SELECT  temp_tickets.*
FROM    temp_tickets
        JOIN ( SELECT   MAX(id) AS id
               FROM     temp_tickets
               GROUP BY ticket_id
               HAVING   COUNT(*) > 1
             ) latest ON latest.id = temp_tickets.id

I suspect it might be possible to come up with a more efficient solution involving user variables but I'll leave that to someone else...

Upvotes: 5

Related Questions