MySQL order by limit confusion

Question

I get a problem today. Here is my jobs table:

select id,name,is_active from jobs order by `is_active` desc

and result is :

But when I want to get first 10 records I select this:

select id,name,is_active from jobs order by `is_active` desc limit 10 offset 0

And result is

Why id is from 14 to 5, it should be 1 to 10. Who can tell me why?

http://stackoverflow.com/questions/8746519/sql-what-is-the-default-order-by-of-queries — Đào Minh Hạt, Mar 07 '17 at 08:18
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html — Ravinder Reddy, Mar 07 '17 at 08:28

score 2 · Accepted Answer · edited Mar 07 '17 at 08:32

2

if you want a deeper order you must explicitly require it otherwise the result will be unpredictable. So if you need the id order too, add it.

select id,name,is_active from jobs order by `is_active` desc, id asc limit 10 offset 0

edited Mar 07 '17 at 08:32

Gurwinder Singh

38,557
6
51
76

answered Mar 07 '17 at 08:17

ScaisEdge

131,976
10
91
107

Thanks. I understand it now. – DengDeng Mar 07 '17 at 08:28

THN · Answer 2 · 2017-03-07T08:42:57.037

1

In MySQL, if not specified explicitly then order is arbitrary. You should try order by id asc.

edited Mar 07 '17 at 08:42

answered Mar 07 '17 at 08:20

THN

3,351
3
26
40

score 1 · Answer 3 · answered Mar 07 '17 at 08:21

1

You need to use multiple columns in ORDER BY, e.g.:

SELECT id, name, is_active 
FROM jobs 
ORDER BY `is_active` DESC id ASC;

answered Mar 07 '17 at 08:21

Darshan Mehta

30,102
11
68
102

score 1 · Answer 4 · answered Mar 07 '17 at 08:26

If you need to keep the result in descending order, and still only want the 10 last id's you should sort your result two times.

This query below will sort the result ascending and limit the result to 10 (that is the query inside the parenthesis). It will still be sorted in ascending order, and we are not satisfied with that, so we sort it one more time. Now we have the newest result on the last row.

select t.id, t.name, t.is_active 
from 
    (select id, name, is_active 
     from jobs 
     order by `is_active` asc limit 10) t 

order by t.is_active desc;

MySQL order by limit confusion

4 Answers4