Different result in order by com limit

Question

Different result in order by com limit

Navigation

#1 by (0 votes)

1

I'm having problems with 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 a problem, if I get the last 3, completely change 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 the id to the order by, so there is a way to create an order in the results

order by    o.date, o.id

postgresql

asked by anonymous 03.10.2018 / 00:02

1 answer

How to leave two screens in an activity, being able to change one of them via code Submit of the form inside a modal view me redirects to the view page

score 0 · Accepted Answer

I think it has to do with this:

When using LIMIT, it is important to use an ORDER BY clause that constrains the result to 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 apparently is to apply ORDER BY outside the LIMIT.