From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Glaesemann <grzm@seespotcode.net>
Cc: Richard Huxton <dev@archonet.com>
Cc: Walter Cruz <walter.php@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
Date: Thu, 15 Feb 2007 12:10:17 -0500
Message-ID: <27009.1171559417@sss.pgh.pa.us> (raw)
In-Reply-To: <57FD2B24-DB43-4EF7-92D1-3C467641AF9D@seespotcode.net>
References: <32cabba0702150319v427d5a25s9a6c7f851576baa2@mail.gmail.com>
<45D46196.2090003@archonet.com>
<57FD2B24-DB43-4EF7-92D1-3C467641AF9D@seespotcode.net>
Michael Glaesemann <grzm@seespotcode.net> writes:
> On Feb 15, 2007, at 22:35 , Richard Huxton wrote:
>> Walter Cruz wrote:
>>> The error: ERROR: for SELECT DISTINCT, ORDER BY expressions must
>>> appear in select list is due to a standart implementarion or a design
>>> decision of postgres?
>>
>> I think ORDER BY is defined to take place after DISTINCT, which
>> means there is no meaningful "number" for it to order by. You could
>> arbitrarily choose the first number encountered, but I can't see
>> what sense it would make to order by them.
> I believe the reason is that DISTINCT depends on a sort to determine
> uniqueness (distinctness), so it's a implementation detail that if
> you're going to include an ORDER BY, you also need to include the
> same columns in the ORDER BY in the DISTINCT clause.
No, there's actually a definitional reason for it. Consider
SELECT DISTINCT x FROM tab ORDER BY y;
For any particular x-value in the table there might be many different y
values. Which one will you use to sort that x-value in the output?
Back in SQL92 they avoided this problem by specifying that ORDER BY
entries had to reference output columns. SQL99 has some messy verbiage
that I think comes out at the same place as our restriction:
A) If K(i) is not equivalent to a <value expression>
immediately contained in any <derived column> in the
<select list> SL of <query specification> QS contained
in QE, then:
I) T shall not be a grouped table.
II) QS shall not specify the <set quantifier> DISTINCT
or directly contain one or more <set function
specification>s.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, grzm@seespotcode.net, dev@archonet.com, walter.php@gmail.com
Subject: Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
In-Reply-To: <27009.1171559417@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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