Skip site navigation (1) Skip section navigation (2)

Re: ORDER BY <field not in return list>

From: "Matt Emmerton" <matt(at)gsicomp(dot)on(dot)ca>
To: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>,"Marc G(dot) Fournier" <scrappy(at)postgresql(dot)org>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: ORDER BY <field not in return list>
Date: 2005-07-26 02:03:48
Message-ID: 01a701c59186$4a243820$1200a8c0@gsicomp.on.ca (view raw or flat)
Thread:
Lists: pgsql-hackers
> On Mon, Jul 25, 2005 at 06:11:08PM -0300, Marc G. Fournier wrote:
> >
> > Just curious as to whether or not a warning or something should be
issued
> > in a case like:
> >
> >   SELECT c.*
> >     FROM company c, company_summary cs
> >    WHERE c.id = cs.id
> >      AND cs.detail = 'test'
> > ORDER BY cs.fullname;
> >
> > Unless I'm missing something, the ORDER BY clause has no effect, but an
> > EXPLAIN shows it does take extra time, obviously ...
>
> Uh, I'd hope it had an effect. Note that RDBMSes have been moving
> towards allowing fields in ORDER BY that aren't in the SELECT list,
> though in the past it was common that anything in ORDER BY had to also
> be in SELECT.

Prior to SQL:1999, the spec required that any column referenced in an ORDER
BY clause must also be referenced in the SELECT.
SQL:1999 (feature E1210-02) relaxed this to allow columns to be specified in
the ORDER BY clause but not in the SELECT.

--
Matt Emmerton


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-07-26 03:26:20
Subject: Re: regression failure on latest CVS
Previous:From: Neil ConwayDate: 2005-07-26 01:41:10
Subject: Re: User's exception plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group