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>
  2000-09-20 18:25 ` Re: [Fwd: Re: no ORDER BY in subselects?] Louis-David Mitterrand <cunctator@apartia.ch>
  2000-09-21 01:29 ` Re: [Fwd: Re: no ORDER BY in subselects?] Philip Warner <pjw@rhyme.com.au>
  2000-09-21 15:50 ` Re: [Fwd: Re: no ORDER BY in subselects?] Frank Bax <fbax@execulink.com>
  2000-09-21 18:35 ` Re: [Fwd: Re: no ORDER BY in subselects?] Philip Warner <pjw@rhyme.com.au>
  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 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>
  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-20 16:20 [Fwd: Re: no ORDER BY in subselects?] Josh Berkus <josh@agliodbs.com>
@ 2000-09-21 01:29 ` Philip Warner <pjw@rhyme.com.au>
  2000-09-22 15:01   ` Re: [Fwd: Re: no ORDER BY in subselects?] 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-20 16:20 [Fwd: Re: no ORDER BY in subselects?] Josh Berkus <josh@agliodbs.com>
  2000-09-21 01:29 ` Re: [Fwd: Re: no ORDER BY in subselects?] Philip Warner <pjw@rhyme.com.au>
@ 2000-09-22 15:01   ` Josh Berkus <josh@agliodbs.com>
  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

* Re: [Fwd: Re: no ORDER BY in subselects?]
  2000-09-20 16:20 [Fwd: Re: no ORDER BY in subselects?] Josh Berkus <josh@agliodbs.com>
@ 2000-09-21 15:50 ` Frank Bax <fbax@execulink.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-20 16:20 [Fwd: Re: no ORDER BY in subselects?] Josh Berkus <josh@agliodbs.com>
@ 2000-09-21 18:35 ` Philip Warner <pjw@rhyme.com.au>
  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


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