mysql with t,MySQL替代T-SQL的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