-1

I have a table representing medals people have earned. A user can have more than one medal.

+-------+-----------------------+------+
| user  | description           | rank |
+-------+-----------------------+------+
| Jake  | Placed in the top 100 | 3    |
+-------+-----------------------+------+
| Jake  | Placed in the top 50  | 2    |
+-------+-----------------------+------+
| Jake  | Placed in the top 10  | 1    |
+-------+-----------------------+------+
| Sam   | Placed in the top 100 | 3    |
+-------+-----------------------+------+

I want to select each user with only the highest rank medal they have earned. I've tried using SELECT * FROM medals GROUP BY user ORDER BY rank ASC. However, I get the following selection when using that query:

+-------+-----------------------+------+
| Jake  | Placed in the top 10  | 3    |
+-------+-----------------------+------+
| Sam   | Placed in the top 100 | 3    |
+-------+-----------------------+------+

The selection I'm trying to get is the following:

+-------+-----------------------+------+
| Jake  | Placed in the top 10  | 1    |
+-------+-----------------------+------+
| Sam   | Placed in the top 100 | 3    |
+-------+-----------------------+------+

How can I query to select the above result?

0 Answers0