Afternoon, all
I just tried this on Oracle, and it wouldn't accept the query. It seems
that once you mention DISTINCT, it won't expand the target list. The actual
message was:
--------------------------------------------------------
SQL> select distinct x from mikea_test order by y;
select distinct x from mikea_test order by y *
ERROR at line 1:
ORA-01791: not a SELECTed expression
--------------------------------------------------------
So, there we have it. I think this is probably the best solution, because
it means that when you do something where the result is not what you would
expect, it forces you to do it another way.
MikeA
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Sent: Thursday, October 21, 1999 3:37 PM
>> To: sszabo@bigpanda.com
>> Cc: Vince Vielhaber; hackers@postgreSQL.org
>> Subject: Re: [HACKERS] distinct. Is this the correct behaviour?
>>
>>
>> sszabo@bigpanda.com writes:
>> > This seems to generally work in postgres for the simple
>> cases I tried:
>> > select x from foo group by x order by min(y)
>>
>> > Now I don't know if there are any hidden gotchas in that
>> (or wierdness
>> > with the spec), but it also feels better to me than using
>> distinct in this
>> > case as well, because it seems to explicitly describe how
>> you want y
>> > ordered.
>>
>> Yes, I like that better too.
>>
>> I wonder if we could/should rewrite all uses of DISTINCT into GROUP
>> BY under-the-hood...
>>
>> regards, tom lane
>>
>> ************
>>