Different result in order by with limit

Question

I have problems with the information obtained in 3 queries.

First a normal select sorted by date:

select      id 
from        app_order o
order by    o.date
; // 30558, 30559, 30560, ...

Then one that should bring me the last record of the previous query:

select      id 
from        app_order o
order by    o.date
limit       1
; // 30558

And here’s a problem, if I take the last three, it completely changes the order of the result:

select      id 
from        app_order o
order by    o.date
limit       3
; // 30559, 30560, 30558 -- aqui já muda completamente do primeiro

I solved the problem by adding id to order by, this way there is how to create an order in the results

order by    o.date, o.id

Answer 1

I think it has to do with that:

When using LIMIT, it is Important to use an ORDER BY clause that constrains the result Rows into a Unique order. Otherwise you will get an unpredictable subset of the query’s Rows. You Might be asking for the Tenth through Twentieth Rows, but Tenth through Twentieth in what Ordering? The Ordering is Unknown, unless you specified ORDER BY.

The solution is to apply ORDER BY outside the LIMIT.