Thread: Unexpected behaviour of ORDER BY and LIMIT/OFFSET
Hi all, I'm having a problem with LIMITed queries, and I don't know is it me who is doing something wrong or Postgres. (Probably me.) 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. From the first query I get Tom, Mike and Marge, and then from the second Marge, Bart and Lisa. Is this expected behavior? My configuration is: CentOS 5.3 and Postgres 8.3.7. Regards, Ognjen
Ognjen Blagojevic <ognjen@etf.bg.ac.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
Tom Lane wrote: > "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. Thank you, Tom, for your prompt response. Regards, Ognjen