CODEWITHSUNDEEP
mysqlsql-order-by
user2129830
user2129830

Reputation: 13

MySQL How to Limit first "order by" results

I have the following sql query: 

"SELECT id, case, value 
FROM tbl1 
WHERE (case > '100' OR case is null) 
ORDER BY case DESC, value DESC"

The result is something like this:

| id   | case     | value  |
 -------------------------- 
| 17   | case1    | value1 |
| 16   | case2    | value2 |
| 14   | case3    | value3 |
| 18   | empty    | value4 |
| 15   | empty    | value5 |

It gives me for example 100 results where "case" is set. an 500 where "case" is empty.

Now i only need the 20 first results where "case" is SET and 100 results where "case" is EMPTY.

If i do the following:

"SELECT id, case, value 
FROM tbl1 ... 
ORDER BY case DESC,value DESC 
LIMIT 0,120"

It gives me all the values where "case" is set... But I only want 20.

Upvotes: 1

Views: 103

Answers (2)

Matt Busche
Matt Busche

Reputation: 14333

you're best off unioning 2 queries

SELECT id, case, value 
FROM tbl1 
WHERE CASE IS NOT NULL
LIMIT 20
UNION
SELECT id, case, value 
FROM tbl1 
WHERE CASE IS NULL
ORDER BY case DESC
LIMIT 100

Upvotes: 1

Olaf Dietsche
Olaf Dietsche

Reputation: 74078

Use a union 

SELECT id, case, value 
FROM tbl1 
WHERE case > '100'
ORDER BY case DESC, value DESC
limit 20
union
SELECT id, case, value 
FROM tbl1 
WHERE case is null
ORDER BY case DESC, value DESC
limit 100

Upvotes: 0

Related Questions