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.

What happens when your data is 10, 10, 10, 10, 10, 10, 10, 5, 4, 3, 2, 1? Will you show the 7 rows with count = 10? Or 7 rows with count = 10 + 4 rows with count between 5 and 2? — Salman Arshad
– Salman Arshad, Commented Jun 2, 2011 at 7:11
@Salman: The description is clear. It will show the seven 10 s. Notice the two 5, 5 in the question. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 2, 2011 at 7:16

ypercubeᵀᴹ · Accepted Answer · 2011-06-02 07:10:52Z

5

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

edited Jun 2, 2011 at 7:10

answered Jun 2, 2011 at 7:05

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ahmet alp balkan · Accepted Answer · 2011-06-02 06:59:57Z

0

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

answered Jun 2, 2011 at 6:59

ahmet alp balkan

45.9k43 gold badges152 silver badges225 bronze badges

Collectives™ on Stack Overflow

mySQL: Variable LIMIT

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related