Select distinct column along with some other columns in MySQL

Question

Using a subquery, you can get the highest id for each name, then select the rest of the rows based on that:

SELECT * FROM table
WHERE id IN (
  SELECT MAX(id) FROM table GROUP BY name
)

If you’d prefer, use MIN(id) to get the first record for each name instead of the last.

It can also be done with an INNER JOIN against the subquery. For this purpose the performance should be similar, and sometimes you need to join on two columns from the subquery.

SELECT
  table.*
FROM 
  table
  INNER JOIN (
    SELECT MAX(id) AS id FROM table GROUP BY name
  ) maxid ON table.id = maxid.id

Leave a Comment Cancel reply