CODEWITHSUNDEEP
mysql
Peter Craig
Peter Craig

Reputation: 7289

Select unique row grouped by column with MySQL

My table looks something like:

ID | Type
01 | a
02 | c
03 | b
04 | a
05 | b
06 | a

I want to output the ID of the last added entries grouped by Type. ID is an auto-increment field so the last added entry is obviously the highest number.

SELECT * FROM `table` GROUP BY `Type` ORDER BY `ID` DESC, `Type` ASC

This groups the rows by Type then sorts by ID so I get IDs 1,2,3 whereas I want to sort first THEN group to get the result: 6,5,2

Upvotes: 0

Views: 253

Answers (3)

Convict
Convict

Reputation: 516

This might be what you're looking for ...

SELECT   MAX(`ID`), `Type`
FROM     `table`
GROUP BY `Type`
ORDER BY `Type` ASC

read as choose the maximum value of ID for each group of Type.

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 882751

Here's a classic way w/o subselects:

SELECT t1.* FROM table t1
LEFT JOIN table t2
  ON (t2.type = t1.type AND t2.id > t1.id)
WHERE t2.id IS NULL

read as "pick rows in the table such that no other row with the same type has a larger id".

Upvotes: 0

Christian Hang-Hicks
Christian Hang-Hicks

Reputation: 2115

If your MySQL version allows subselects (and 5.x does), this should work:

SELECT * 
    FROM (SELECT * FROM `table` ORDER BY `id` DESC) AS tmp 
    GROUP BY `type` 
    ORDER BY `type` ASC;

Upvotes: 1

Related Questions