Received: from acheron.rime.com.au (root@albatr.lnk.telstra.net [139.130.54.222]) by hub.org (8.10.1/8.10.1) with ESMTP id e8LHYOs94057 for ; Thu, 21 Sep 2000 13:34:25 -0400 (EDT) Received: from oberon (Oberon.rime.com.au [203.8.195.100]) by acheron.rime.com.au (8.9.3/8.9.3) with SMTP id DAA09145; Fri, 22 Sep 2000 03:33:33 +1000 Message-Id: <3.0.5.32.20000922043517.02f093d0@mail.rhyme.com.au> X-Sender: pjw@mail.rhyme.com.au X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.5 (32) Date: Fri, 22 Sep 2000 04:35:17 +1000 To: Frank Bax , pgsql-sql@postgresql.org From: Philip Warner Subject: Re: [Fwd: Re: no ORDER BY in subselects?] In-Reply-To: <3.0.6.32.20000921115010.0097e1b0@execulink.com> References: <3.0.5.32.20000921112954.00c9be00@mail.rhyme.com.au> <39C8E3C9.D1CFFDB3@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Archive-Number: 200009/173 At 11:50 21/09/00 -0400, Frank Bax wrote: >At 11:29 AM 9/21/00 +1000, you wrote: >>The main reason I use them is to find the 'next' or 'previous' record in a >>list (eg. next date, next ID). eg. >> >> select , (select ID from table where id > this.id >> order by id asc limit 1) as next_id ... > >Doesn't this give the same result (without order by): > >> select , (select min(ID) from table where id > this.id) as >next_id Yes, but I don't think PostgreSQL is smart enough to use indexes to evaluate the Min() function. Also, min/max does not work quite so well with a slightly more complex example: select , (select ID from table where date_field > this.date_field order by date_field asc limit 1) as next_id ... (ie. if the date_field and id are not correlated, but you want the id corresponding to the next date). ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/