mySQL: Variable LIMIT

I'm using a SQ开发者_开发百科L-statement to retrieve the top 5 entries in a list:

SELECT ... FROM table ORDER BY someColumn DESC LIMIT 5

The result will look something like this:

Name       Count
Person B   10
Person D   8
Person A   5
Person E   5
Person C   4

If there are more results with a value like the fifth entry in the list (4 in the example), I would like to show them too. Is there a way to achieve this with a single query?

So let's say the complete lists looks like this:

Name       Count
Person B   10
Person D   8
Person A   5
Person E   5
Person C   4
Person H   4
Person I   4
------------
Person G   3
Person F   1
Person J   1

How can I modify my query to return the first seven results? Obviosuly I can't use LIMIT 7 or WHERE Count >= 4, as I don't know that in advance.

You mean you want the fist five results but also all other results which are tied in 5th place, right? One solution is to get the 5th result first - using a subquery - and then get what you need:

SELECT ...
FROM table
WHERE someColumn >=
    ( SELECT someColumn 
      FROM table 
      ORDER BY someColumn DESC 
      LIMIT 4, 1                      <-- only the 5th 
    )
ORDER BY someColumn DESC 

Two solutions:

Sol 1: Use LIMIT 100 or so, and eliminate unwanted rows in your program.

Sol 2: Execute two queries, most probaby that will be more costly:

SELECT * FROM (SELECT ... FROM table ORDER BY
  someColumn DESC LIMIT 5) 
  UNION (SELECT ... FROM table ORDER BY
         someColumn DESC LIMIT 50, 5);