Re: [SQL] 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. " 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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Controlling Reuslts with Limit
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 [EMAIL PROTECTED] 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: [SQL] Controlling Reuslts with Limit
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 [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Controlling Reuslts with Limit
Najm Hashmi [EMAIL PROTECTED] 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
