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

Question

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.

Answer 1

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