1

I have a query that follows this format:

(SELECT t.column1, t.column2
FROM table t 
WHERE t.status = 1 
LIMIT 10)
UNION
(SELECT t.column1, t.column2 
FROM table t
WHERE t.status = 2 
LIMIT 10)

The end result is that I need to have 20 rows. If the first SELECT statement can only find 9 rows with t.status = 1, then I would like the second SELECT statement to use LIMIT 11 instead of LIMIT 10

I am using PHP to write and run the query, but I am looking for something that will execute within MySQL so I can run it all as one query.

Any ideas would be greatly appreciated.

Chris
  • 5,485
  • 15
  • 68
  • 130
  • possible duplicate of [Combining UNION and LIMIT operations in MySQL query](http://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query) – Gerben Jacobs Mar 25 '14 at 14:23
  • This isn't the same thing. I am asking if I can place limits on each SELECT statement but still guarantee that I will get 20 rows in my final result. – Chris Mar 25 '14 at 14:29
  • Yeah, so was that question. But the answer is wrong (even though it's accepted). I don't know the answer to this question though.. – Gerben Jacobs Mar 25 '14 at 14:31
  • Ok well if you can remove the "This question may already have an answer here:" from the top of the post, that would be great. – Chris Mar 25 '14 at 14:34

1 Answers1

0

Add one more limit 'outside' with the total count and use the same for the limit of the 2-nd query.

(
SELECT t.column1, t.column2
FROM table t 
WHERE t.status = 1 
LIMIT 10 # 1/2 of total rows
)
UNION
(
SELECT t.column1, t.column2 
FROM table t
WHERE t.status = 2 
LIMIT 20 # total rows
)
LIMIT 20 # total rows
Vatev
  • 7,493
  • 1
  • 32
  • 39