Re: Unexpected behaviour of ORDER BY and LIMIT/OFFSET

Ognjen Blagojevic <ognjen(at)etf(dot)bg(dot)ac(dot)yu> writes:
> When I browse through the list of employees:

> id id_dept name
> -------------------
> 1 1 Tom
> 2 1 Mike
> 3 2 Meggie
> 4 2 Marge
> 5 3 Bart
> 6 3 Lisa
> 7 4 Homer

> using LIMITed selects like:

> SELECT * FROM employee ORDER BY id_dept LIMIT 3
> SELECT * FROM employee ORDER BY id_dept LIMIT 3 OFFSET 3
> SELECT * FROM employee ORDER BY id_dept LIMIT 3 OFFSET 6

> it seems that Meggie is not in the result list on any of the SELECTs.

"ORDER BY id_dept" isn't a unique sort key. In this example the
implementation is free to return Meggie and Marge in either order,
and the ordering can indeed vary depending on the LIMIT/OFFSET values.

Moral: don't use LIMIT/OFFSET without a fully specified sort order.

regards, tom lane