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

Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Walter Cruz <walter(dot)php(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?
Date: 2007-02-15 14:04:04
Message-ID: 57FD2B24-DB43-4EF7-92D1-3C467641AF9D@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-sql
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



In response to

Responses

pgsql-sql by date

Next:From: Ezequias Rodrigues da RochaDate: 2007-02-15 16:17:46
Subject: Retrieving 'Credit' when 'C'
Previous:From: Richard HuxtonDate: 2007-02-15 13:35:18
Subject: Re: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql?

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