Thread: Controlling Reuslts with Limit
Hi,
I was reading through Bruce's on line . I found follwing bit unclear...
"Notice that each query uses ORDER BY . Although this clause is not required,
LIMIT without ORDER BY returns random rows from the query, which would be
useless. "
When I run a query several time I get the same results as given
flipr=# select song_id from songs limit 5;
song_id
---------
945
946
947
948
949
(5 rows)
flipr=# select song_id from songs limit 5;
song_id
---------
945
946
947
948
949
(5 rows)
flipr=# select song_id from songs limit 5;
song_id
---------
945
946
947
948
949
(5 rows)
flipr=# select song_id from songs limit 5;
song_id
---------
945
946
947
948
949
(5 rows)
flipr=# select song_id from songs limit 5;
song_id
---------
945
946
947
948
949
(5 rows)
flipr=# select song_id from songs limit 5;
song_id
---------
945
946
947
948
949
I just want to know what exatly --"LIMIT without ORDER BY returns random rows
from the query" --means
Regards
Attachment
> Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " It means there is no guarantee which rows will be returned. You may get the rows you want, or you may not. Without the ORDER BY, the backend can return any five rows it wishes. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " > > When I run a query several time I get the same results as given ... > I just want to know what exatly --"LIMIT without ORDER BY returns random rows > from the query" --means I don't think it is actually random. It just that the order is not defined and other events may change the order. I believe that without an ORDER BY or other clauses that cause an index to be used that the database tends to return rows in the order stored on disk. This order tends to be the order in which rows were added. My observation is this ordering is faily stable and it seems to survive a database reload. Just don't rely on it. There is a CLUSTER command to change the physical ordering.
My understanding: because you return a subset instead of a single value, so between 2 select ... limit ... queries. if you delete a record(say song_id=947) then insert it again. then results are different. So for a multiple users db, you should use oder by when you use limit. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com On Sat, 24 Feb 2001, Najm Hashmi wrote: > Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " > > When I run a query several time I get the same results as given > flipr=# select song_id from songs limit 5; > song_id > --------- > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > --------- > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > --------- > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > --------- > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > --------- > 945 > 946 > 947 > 948 > 949 > (5 rows) > > flipr=# select song_id from songs limit 5; > song_id > --------- > 945 > 946 > 947 > 948 > 949 > I just want to know what exatly --"LIMIT without ORDER BY returns random rows > from the query" --means > Regards >
> I don't think it is actually random. It just that the order is not defined > and other events may change the order. I believe that without an ORDER BY > or other clauses that cause an index to be used that the database tends to > return rows in the order stored on disk. This order tends to be the order > in which rows were added. My observation is this ordering is faily stable > and it seems to survive a database reload. Just don't rely on it. There is > a CLUSTER command to change the physical ordering. Yes, usually it is the heap order, but if you do "col > 12" you may get it in index order by the column indexes, or you may not, depending on the constant, the size of the table, vacuum, vacuum analyze, etc. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Najm Hashmi <najm@mondo-live.com> writes:
> I just want to know what exatly --"LIMIT without ORDER BY returns random rows
> from the query" --means
It means the results aren't guaranteed. It doesn't mean that the exact
same query run under the exact same conditions by the exact same version
of Postgres won't return the same results every time. Especially not
one that's too simple to have more than one possible execution plan...
regards, tom lane
It returns the first five rows it finds. Running the same query over again if there are no updates is safe, but if the table is updated there is the possibility it would find a different five rows. If the query would do a seq scan and you updated a row, the rows would be in a different order in the heap file and so you'd get a different ordering of rows... On Sat, 24 Feb 2001, Najm Hashmi wrote: > Hi, > I was reading through Bruce's on line . I found follwing bit unclear... > > "Notice that each query uses ORDER BY . Although this clause is not required, > LIMIT without ORDER BY returns random rows from the query, which would be > useless. " > > When I run a query several time I get the same results as given > flipr=# select song_id from songs limit 5; > song_id > --------- > 945 > 946 > 947 > 948 > 949 > (5 rows) > ...