Select distinct column along with some other columns in MySQL

Question

I can't seem to find a suitable solution for the following (probably an age old) problem so hoping someone can shed some light. I need to return 1 distinct column along with other non distinct columns in mySQL.

I have the following table in mySQL:

id      name       destination     rating     country
----------------------------------------------------
1       James      Barbados        5          WI
2       Andrew     Antigua         6          WI
3       James      Barbados        3          WI
4       Declan     Trinidad        2          WI
5       Steve      Barbados        4          WI
6       Declan     Trinidad        3          WI

I would like SQL statement to return the DISTINCT name along with the destination, rating based on country.

id      name       destination     rating     country
----------------------------------------------------
1       James      Barbados        5          WI
2       Andrew     Antigua         6          WI
4       Declan     Trinidad        2          WI
5       Steve      Barbados        4          WI

As you can see, James and Declan have different ratings, but the same name, so they are returned only once.

The following query returns all rows because the ratings are different. Is there anyway I can return the above result set?

SELECT (distinct name), destination, rating 
  FROM table 
 WHERE country = 'WI' 
 ORDER BY id

Michael Berkowski · Accepted Answer · 2013-02-12T00:32:54.130

33

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

edited Feb 12 '13 at 00:32

answered Dec 18 '11 at 13:59

Michael Berkowski

267,341
46
444
390

As hinted at here https://stackoverflow.com/a/13617822/2126442 , if you need to have multiple "distinct" columns, you would group multiple times: `SELECT name, destination, MAX(id) AS id FROM table GROUP BY name, destination;` (or wrapped by `SELECT * FROM table WHERE id IN (…)`). – WoodrowShigeru Apr 28 '20 at 09:44

score 5 · Answer 2 · answered Dec 18 '11 at 14:01

The problem is that distinct works across the entire return set and not just the first field. Otherwise MySQL wouldn't know what record to return. So, you want to have some sort of group function on rating, whether MAX, MIN, GROUP_CONCAT, AVG, or several other functions.

Michael has already posted a good answer, so I'm not going to re-write the query.

Ricardo Souza · Answer 3 · 2022-10-27T23:15:53.670

4

You can do a GROUP BY clause:

SELECT MIN(id) AS id, name, destination, AVG(rating) AS rating, country
FROM TABLE_NAME
GROUP BY name, destination, country

This query would perform better in large datasets than the subquery alternatives and it can be easier to read as well.

edited Oct 27 '22 at 23:15

answered Dec 18 '11 at 14:09

Ricardo Souza

16,030
6
37
69

score 4 · Answer 4 · answered Mar 10 '12 at 11:09

I agree with @rcdmk . Using a DEPENDENT subquery can kill performance, GROUP BY seems more suitable provided that you have already INDEXed the country field and only a few rows will reach the server. Rewriting the query giben by @rcdmk , I added the ORDER BY NULL clause to suppress the implicit ordering by GROUP BY, to make it a little faster:

SELECT MIN(id) as id, name, destination as rating, country 
FROM table WHERE country = 'WI' 
GROUP BY name, destination ORDER BY NULL

Select distinct column along with some other columns in MySQL

4 Answers4

Linked