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?