pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[Fwd: Re: no ORDER BY in subselects?]
6+ messages / 4 participants
[nested] [flat]
* [Fwd: Re: no ORDER BY in subselects?]
@ 2000-09-20 16:20 Josh Berkus <josh@agliodbs.com>
0 siblings, 4 replies; 6+ messages in thread
From: Josh Berkus @ 2000-09-20 16:20 UTC (permalink / raw)
To: pgsql-sql
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 information technology 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Fwd: Re: no ORDER BY in subselects?]
@ 2000-09-20 18:25 Louis-David Mitterrand <cunctator@apartia.ch>
parent: Josh Berkus <josh@agliodbs.com>
3 siblings, 0 replies; 6+ messages in thread
From: Louis-David Mitterrand @ 2000-09-20 18:25 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Fwd: Re: no ORDER BY in subselects?]
@ 2000-09-21 01:29 Philip Warner <pjw@rhyme.com.au>
parent: Josh Berkus <josh@agliodbs.com>
3 siblings, 1 reply; 6+ messages in thread
From: Philip Warner @ 2000-09-21 01:29 UTC (permalink / raw)
To: pgsql-sql
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 |/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Fwd: Re: no ORDER BY in subselects?]
@ 2000-09-21 15:50 Frank Bax <fbax@execulink.com>
parent: Josh Berkus <josh@agliodbs.com>
3 siblings, 0 replies; 6+ messages in thread
From: Frank Bax @ 2000-09-21 15:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Fwd: Re: no ORDER BY in subselects?]
@ 2000-09-21 18:35 Philip Warner <pjw@rhyme.com.au>
parent: Josh Berkus <josh@agliodbs.com>
3 siblings, 0 replies; 6+ messages in thread
From: Philip Warner @ 2000-09-21 18:35 UTC (permalink / raw)
To: Frank Bax <fbax@execulink.com>; pgsql-sql
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 |/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [Fwd: Re: no ORDER BY in subselects?]
@ 2000-09-22 15:01 Josh Berkus <josh@agliodbs.com>
parent: Philip Warner <pjw@rhyme.com.au>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2000-09-22 15:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2000-09-22 15:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-20 16:20 [Fwd: Re: no ORDER BY in subselects?] Josh Berkus <josh@agliodbs.com>
2000-09-20 18:25 ` Louis-David Mitterrand <cunctator@apartia.ch>
2000-09-21 01:29 ` Philip Warner <pjw@rhyme.com.au>
2000-09-22 15:01 ` Josh Berkus <josh@agliodbs.com>
2000-09-21 15:50 ` Frank Bax <fbax@execulink.com>
2000-09-21 18:35 ` Philip Warner <pjw@rhyme.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox