MySQL's alternative to T-SQL's WITH TIES

I have a table from which I want to get the top N records. The records are ordered by values and some records have the same values. What I'd like to do here is to get a list of top N records, including the tied ones. This is what's in the table:

+-------+--------+
| Name  | Value  |
+-------+--------+
| A     | 10     |
| B     | 30     |
| C     | 40     |
| D     | 40     |
| E     | 20     |
| F     | 50     |
+-------+--------+

Now if I want to get the top 3 like so

SELECT * FROM table ORDER BY Value DESC LIMIT 3

I get this:

+-------+--------+
| Name  | Value  |
+-------+--------+
| F     | 50     |
| C     | 40     |
| D     | 40     |
+-------+--------+

What I would like to get is this

+-------+--------+
| Name  | Value  |
+-------+--------+
| F     | 50     |
| C     | 40     |
| D     | 40     |
| B     | 30     |
+-------+--------+

I calculate the rank of each record so what I would really like is to get the first N ranked records instead of the first N records ordered by value. This is how I calculate the rank:

SELECT Value AS Val, (SELECT COUNT(DISTINCT(Value))+1 FROM table WHERE Value > Val) as Rank

In T-SQL something like this is achievable by doing this:

SELECT TOP 3 FROM table ORDER BY Value WITH TIES

Does anyone have an idea how to do this in MySQL? I understand it could be done with subqueries or temporary tables but I don't have enough knowledge to accomplish this. 开发者_运维技巧I'd prefer a solution without using temporary tables.

---

Does this work for you?

select Name, Value from table where Value in (
    select distinct Value from table order by Value desc limit 3
) order by Value desc

Or perhaps:

select a.Name, a.Value 
from table a
join (select distinct Value from table order by Value desc limit 3) b
     on a.Value = b.Value

---

select a.Name, a.Value 
from table a
join (select Value from table order by Value desc limit 3) b
     on a.Value = b.Value

This is like @Fosco's answer, but without DISTINCT in the subquery. His version returns the players with the top N scores, not the top N players (plus ties). E.g. if the scores are 50, 50, 50, 40, 40, 30, 20, he'll return 6 players (3x50, 2x40, 1x30), but you presumably just want 3x50.

---

Starting with MySQL 8, you can use window functions to emulate the WITH TIES semantics, by filtering on RANK(). For example:

SELECT Name, Value
FROM (
  SELECT Name, Value, RANK() OVER (ORDER BY Value DESC) AS rk
  FROM table
) t
WHERE rk <= 3

Note that when reading your question more closely, this doesn't do exactly what you seem to want, but it does exactly what T-SQL can do through the TOP n WITH TIES clause.