FInd two highest values for various rows in MySQL table

Question

I have a MySQL table, sales, with this contents:

+----+-------+--------+
| id | name  | amount |
+----+-------+--------+
|  1 | Alice |     12 |
|  2 | Bob   |     10 |
|  3 | Alice |     14 |
|  4 | Bob   |      2 |
|  5 | Alice |      6 |
|  6 | Bob   |     23 |
|  7 | Bob   |      5 |
|  8 | Alice |     12 |
+----+-------+--------+

For each name I want to find the two rows with the highest amount. In other words, I want a query that returns this result:

+----+-------+--------+
| id | name  | amount |
+----+-------+--------+
|  3 | Alice |     14 |
|  1 | Alice |     12 |
|  6 | Bob   |     23 |
|  2 | Bob   |     10 |
+----+-------+--------+

(Only one of rows 1 and 8 should be included in the result. It doesn't matter which.)

How can I do that?

EDIT

Row 8 in the table added to clarify the handling of ties.

Answer 1

If you are running MySQL 8.0, just use window functions:

select *
from (
    select s.*,
        rank() over(partition by name order by amount desc) rn
    from sales s
) s
where rn <= 2
order by name, amount desc

This allows top 2 ties, if any.

In earlier versions, one option uses a subquery for filtering:

select *
from sales s
where (select count(*) from sales s1 where s1.name = s.name and s1.amount > s.amount) < 2
order by name, amount desc