sql如何先排序再去重
场景
有一张得分表(score)，记录了用户每次的得分，同一个人可能有多个得分。
| id | name | score |
|---|---|---|
| 1 | tom | 45 |
| 2 | jack | 78 |
| 3 | tom | 34 |
| . | . | . |
需求：找出分数最高的前5个人。
SQL1
首先我们写个最简单的sql：
select
id, name, score
from
score
order by
score desc
limit 5;
如果sql这样写，结果可能是：
| id | name | score |
|---|---|---|
| 2 | jack | 78 |
| 1 | tom | 45 |
| 3 | tom | 34 |
排序了，但是没有去重
SQL2
那么我们加上去重：
select
distinct name
from
score
order by
score desc
limit 5;
首先第一点是这个sql未必能执行。在一些数据库版本，这个sql可以被执行，在一些版本则会提示你order by的字段必须在distinct中存在(见SQL3)。
但是即使能执行，这个sql也得不到预期结果。原因是distinct优先于order by 被数据库执行。
在执行distinct name的时候，如上文中的数据。是取id=1的数据，还是id=3的数据呢？其实这是数据库自行决定的。因此，可能会不正确选择数据。
比如真的执行这个sql，可能去重的结果是：
| id | name | score |
|---|---|---|
| 2 | jack | 78 |
| 3 | tom | 34 |
然后再执行一个order by，就会认为第一名是jack78分，第二名是tom34分。然而其实tom应该是45分，这个45分就在数据库执行distinct的时候被错误的丢弃了，毕竟先执行distinct的时候不知道你到底要哪个数据。
SQL3
那么我们把score加入select中呢？
select
distinct name, score
from
score
order by
score desc
limit 5;
很明显，这样写的执行结果和我们预期不符。因为如果写：distinct name,score实际上是对name和score一起去重。比如name都是jack，score都是45。那么这行就会被去掉。
但是问题是正因为把score当做去重的条件了。所以对于同名的人，比如都叫tom，会因为其有两个分数，导致不能被去重，从而保留两行记录。结果就是好像没有去重。
SQL4
那我不用distinct，用group by进行去重可以吗？
select
name
from
score
group by
name
order by
score desc
limit 5;
也不行，因为在group by的时候，数据库还是不知道对两行name一样的数据，究竟应该留下哪一行。
SQL5
正确的写法：
select
name
from
score
group by
name
order by
max(score) desc
limit 5;
这样写，在执行group by的时候，数据库就知道要保留score最大的那一行了。
