Search code examples
mysqlselectgreatest-n-per-group

Mysql select distinct

I am trying to select of the duplicate rows in mysql table it's working fine for me but the problem is that it is not letting me select all the fields in that query , just letting me select the field name i used as distinct , lemme write the query for better understading

mysql_query("SELECT DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")

mysql_query("SELECT * , DISTINCT ticket_id FROM temp_tickets ORDER BY ticket_id")

1st one is working fine

now when i am trying to select all fields i am ending up with errors

i am trying to select the latest of the duplicates let say ticket_id 127 is 3 times on row id 7,8,9 so i want to select it once with the latest entry that would be 9 in this case and this applies on all the rest of the ticket_id's

Any idea thanks

Solution

  • Are you looking for "SELECT * FROM temp_tickets GROUP BY ticket_id ORDER BY ticket_id ?

    UPDATE 

    SELECT t.* 
FROM 
(SELECT ticket_id, MAX(id) as id FROM temp_tickets GROUP BY ticket_id) a  
INNER JOIN temp_tickets t ON (t.id = a.id)