CODEWITHSUNDEEP
mysql
DengDeng
DengDeng

Reputation: 543

MySQL order by limit confusion

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 :

enter image description here

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

enter image description here

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

Upvotes: 2

Views: 94

Answers (4)

THN
THN

Reputation: 3611

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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 2

Yıldız Ardal
Yıldız Ardal

Reputation: 83

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;

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30809

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;

Upvotes: 1

Related Questions