Обсуждение: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
CREATE TABLE test
( id int4 NOT NULL DEFAULT nextval('teste_id_seq'::regclass), name varchar, number int4
)
WITHOUT OIDS;
The data:
1;"walter";1
2;"walter";1
3;"walter";1
4;"walter";1
5;"walter";2
6;"walter";3
7;"rodrigo";1
8;"rodrigo";2
9;"rodrigo";3
The query:
SELECT distinct name from test order by number
(well, I think that que query doesn't make any sense, but raises the error :) )
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?
The comentary on parse_clause.c looks like the second option. I'm right?
[]'s
- Walter
Walter Cruz wrote: > > SELECT distinct name from test order by number > > (well, I think that que query doesn't make any sense, but raises the > error :) ) > > 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. -- Richard Huxton Archonet Ltd
On Feb 15, 2007, at 22:35 , Richard Huxton wrote: > Walter Cruz wrote: >> SELECT distinct name from test order by number >> (well, I think that que query doesn't make any sense, but raises >> the error :) ) >> 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. Though I suspect Richard is right that ORDER BY takes place after DISTINCT. (My cursory attempt at parsing the SQL 2003 draft failed me.) On further thought, I bet SELECT DISTINCT name FROM test ORDER BY name, number fails with a different error, one directly supporting Richard's conclusion. Michael Glaesemann grzm 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
<derivedcolumn> in the <select list> SL of <query specification> QS contained
inQE, then:
I) T shall not be a grouped table.
II) QS shall not specify the <set quantifier> DISTINCT or directly contain
oneor more <set function specification>s.
regards, tom lane
Thanks Tom, Thank all :) Maybe the commentary on parse_clase.c (beggining with "Now, DISTINCT list consists of all non-resjunk") needs to be updated - In the comment, looks likes this is a postgresql limitation. []'s - Walter On 2/15/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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 >