Skip site navigation (1) Skip section navigation (2)

Re: Unexpected behaviour of ORDER BY and LIMIT/OFFSET

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ognjen Blagojevic <ognjen(at)etf(dot)bg(dot)ac(dot)yu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Unexpected behaviour of ORDER BY and LIMIT/OFFSET
Date: 2009-05-14 18:50:40
Message-ID: 17479.1242327040@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: JORGE MALDONADODate: 2009-05-14 19:03:20
Subject: INSERTING "NEW LINES" IN A SELECT STATEMENT
Previous:From: Ognjen BlagojevicDate: 2009-05-14 18:28:01
Subject: Unexpected behaviour of ORDER BY and LIMIT/OFFSET

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group