Select distinct column along with some other columns in MySQL

mysql select distinct
user1038814 picture user1038814 · Dec 18, 2011 · Viewed 55.1k times · Source

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

Answer

Michael Berkowski picture Michael Berkowski · Dec 18, 2011

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

Related questions

mysql query: SELECT DISTINCT column1, GROUP BY column2

Right now I have the following query: SELECT name, COUNT(name), time, price, ip, SUM(price) FROM tablename WHERE time >= $yesterday AND time <$today GROUP BY name And what I'd like to do is add a DISTINCT by …

Read More
MySQL Counting Distinct Values on Multiple Columns

I wrote a script that runs each time a user logs into a computer in our domain. This script makes a record of the user as well as the computer they logged into. Any number of users can log into …

Read More
Converting SELECT DISTINCT ON queries from Postgresql to MySQL

I've been using PostgreSQL and now migrating to MySQL. In my queries, I'm using PostgreSQL's SELECT DISTINCT ON (col1, col2, col3), I was wondering if there is any counterpart of this statement in MySQL.

Read More