Thread: Controlling Reuslts with Limit

Controlling Reuslts with Limit

From
Najm Hashmi
Date:
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

Re: Controlling Reuslts with Limit

From
Bruce Momjian
Date:
> 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
 


Re: Controlling Reuslts with Limit

From
"Bryan White"
Date:

> 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.



Re: Controlling Reuslts with Limit

From
Jie Liang
Date:
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
> 



Re: Controlling Reuslts with Limit

From
Bruce Momjian
Date:
> 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
 


Re: Controlling Reuslts with Limit

From
Tom Lane
Date:
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


Re: Controlling Reuslts with Limit

From
Stephan Szabo
Date:
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)
> ...