Select one row from multiple rows based on one field

Question

I have 1 relevant table for this question, award :

----------------------------------------
| award                                |
----------------------------------------
| id | name | URL | country_id | point |
----------------------------------------
  1  | A    | a   | 1          | 120
  2  | A    | b   | 0          | 60
  3  | A    | b   | 2          | 80
  4  | B    | c   | 0          | 50
  5  | B    | d   | 1          | 70

I want to be able to get for a list of given countries, the corresponding different awards (a same award has the same name) with the highest value for the column point.

So I tried ordering my request by point DESC, and grouping by name, but it didn't do the trick, I didn't get the right number of points nor the right URL.

I know I could do that easily by getting all the data from the table then keeping only the row with the best number of points, but there must be a way to do that with SQL only !

The request I tried was :

SELECT name, URL, point FROM award WHERE country_id IN (0,1) ORDER BY point DESC GROUP BY name

markwatsonatx · Accepted Answer · 2016-04-17 05:50:46Z

1

This should get you what you are looking for:

SELECT t1.* FROM award AS t1
   INNER JOIN (SELECT name, MAX(point) AS point
      FROM award
      WHERE country_id IN (0,1)
      GROUP BY name) AS t2
   ON t1.name=t2.name
      AND t1.point=t2.point;

Here is how it breaks down:

This is joining the award table (identified as t1) with a subquery that is identified as t2. The subquery looks like this:

SELECT name, MAX(point) AS point
    FROM award
    WHERE country_id IN (0,1)
    GROUP BY name

The subquery will return the maximum amount of points for each name. Here are the results of the subquery:

+------+-------+
| name | point |
+------+-------+
| A    |   120 |
| B    |    70 |
+------+-------+

Obviously this doesn't include the URL. If it did then URL would have to be in the GROUP BY and then you're entries would be the unique combination of name and URL (and then you wouldn't get the max point value).

The next step is to essentially join to this temporary table to get the rows in the award table that match.

INNER JOIN (...) AS t2
    ON t1.name=t2.name
       AND t1.point=t2.point;

The join finds the rows in the award table where name and point match name and point in the results of the subquery. This gives us the full rows that we want - and we can now access the URL. The result looks like this:

+------+------+------+------------+-------+
| id   | name | URL  | country_id | point |
+------+------+------+------------+-------+
|    1 | A    | a    |          1 |   120 |
|    5 | B    | d    |          1 |    70 |
+------+------+------+------------+-------+

Also, at this point you can order by an column in the award table you want, for example name:

ORDER BY t1.name

or highest point value:

ORDER BY t1.point DESC

edited Apr 17, 2016 at 5:50

answered Apr 17, 2016 at 5:24

markwatsonatx

3,5012 gold badges24 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hellimac Over a year ago

It worked for me, but there could be a problem if there are different rows with the same name and the same number of points : the URL could be wrong (not from the good row).

markwatsonatx Over a year ago

Which row would you want to return in that case (i.e. which is the "good row")? If you include country_id in the group by and join then you will get the max points per name / country. Not sure if that's what you are looking for.

keronconk · Accepted Answer · 2016-04-17 05:43:15Z

0

If you want the point desc, try it

    SELECT name, country_id, point
FROM `award`
WHERE country_id IN (0,1)
GROUP BY name, country_id
ORDER BY name, point DESC

result

    name |country_id  |point
    =====|============|===========  
    A    |1           |120
    A    |0           |60
    B    |1           |70
    B    |0           |50

answered Apr 17, 2016 at 5:43

keronconk

3592 silver badges10 bronze badges

1 Comment

hellimac Over a year ago

I guess my question wasn't clear enough. The aim here is to get only one row per name, the one with the highest value in point.

Collectives™ on Stack Overflow

Select one row from multiple rows based on one field

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related