Thread: [Fwd: Re: no ORDER BY in subselects?]
Ooops, posted this to Phillip rather than the list, sorry Phillip ... Folks, Philip Warner wrote: > > At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > > > >ERROR: parser: parse error at or near "order" > > > >Aren't ORDER BY clauses allowed in subselects? > > > > It is a very very sad fact, but, no, they're not. Hmmmm ... can't say as I've ever seen an ORDER BY in a subselect before. Why would you want one? And if you do want one, Louis-David, you can always use a temporary table as previously described. -Josh -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
On Wed, Sep 20, 2000 at 09:20:25AM -0700, Josh Berkus wrote: > > At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > > > > > >ERROR: parser: parse error at or near "order" > > > > > >Aren't ORDER BY clauses allowed in subselects? > > > > > > > It is a very very sad fact, but, no, they're not. > > Hmmmm ... can't say as I've ever seen an ORDER BY in a subselect before. > Why would you want one? If only to do a "LIMIT 1" on it. But this is probably considered very ugly to exprienced DB users (I'm only recently self-taught on that subject). > And if you do want one, Louis-David, you can always use a temporary > table as previously described. I found another workaround to the problem, finally. Thanks -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org Your mouse has moved. Windows must be restarted for the change to take effect. Reboot now?
At 09:20 20/09/00 -0700, Josh Berkus wrote: >Ooops, posted this to Phillip rather than the list, sorry Phillip ... > >Folks, > >Philip Warner wrote: >> >> At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: >> > >> >ERROR: parser: parse error at or near "order" >> > >> >Aren't ORDER BY clauses allowed in subselects? >> > >> >> It is a very very sad fact, but, no, they're not. > >Hmmmm ... can't say as I've ever seen an ORDER BY in a subselect before. >Why would you want one? > The main reason I use them is to find the 'next' or 'previous' record in a list (eg. next date, next ID). eg. select <whatever>, (select ID from table where id > this.id order by id asc limit 1) as next_id ... OR select <whatever>, (select Start_Date from table where Start_Date > this.Start_Date Order By Start_Date asc limit 1) as End_Date >And if you do want one, Louis-David, you can always use a temporary >table as previously described. It is A LOT less clean. The fact that Dec RDB, Oracle and SQL/Server all allow it probably means that there is a reasonable user base out there who think it's a good idea. ---------------------------------------------------------------- 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 |/
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 <whatever>, (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 <whatever>, (select min(ID) from table where id > this.id) as next_id Frank
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 <whatever>, (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 <whatever>, (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 <whatever>, (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 |/
Phillip, > The main reason I use them is to find the 'next' or > 'previous' record in a > list (eg. next date, next ID). eg. > The fact that Dec RDB, Oracle and SQL/Server all allow it > probably means > that there is a reasonable user base out there who think > it's a good idea. Makes sense. Fortunately, in PGSQL there's another function to grab the next ID. As for dates ... hmmm... there we come up against the "no subselect in FROM" bug, don't we? I get the problem. (Personally, I've always SELECTed into a variable in MSSQL rather than using the syntac you're suggesting. I've found that the order that the MSSQL chooses to execute query segments in can cause some unpredicatble results ... ) -Josh Berkus