mySQL: Variable LIMIT

Question

I'm using a SQL-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.

Answer 1

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 

Answer 2

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);