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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
