From: | Neil Conway <neilc(at)samurai(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | ORDER BY and DISTINCT ON |
Date: | 2003-12-12 23:39:20 |
Message-ID: | 87ad5xppw7.fsf@mailbox.samurai.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
We reject the following query:
nconway=# create table abc (a int, b int, c int);
CREATE TABLE
nconway=# select distinct on (a) a, b, c from abc order by b, c, a;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressions
This works fine, of course:
nconway=# select distinct on (a) a, b, c from abc order by a, b, c;
a | b | c
---+---+---
(0 rows)
src/backend/parser/parse_clause.c notes:
/*
* If the user writes both DISTINCT ON and ORDER BY, then the
* two expression lists must match (until one or the other
* runs out). Otherwise the ORDER BY requires a different
* sort order than the DISTINCT does, and we can't implement
* that with only one sort pass (and if we do two passes, the
* results will be rather unpredictable). However, it's OK to
* have more DISTINCT ON expressions than ORDER BY
* expressions; we can just add the extra DISTINCT values to
* the sort list, much as we did above for ordinary DISTINCT
* fields.
*
* Actually, it'd be OK for the common prefixes of the two
* lists to match in any order, but implementing that check
* seems like more trouble than it's worth.
*/
Does this strike anyone else as being wrong?
-Neil
From | Date | Subject | |
---|---|---|---|
Next Message | Kurt Roeckx | 2003-12-12 23:42:58 | Walker/mutator prototype. |
Previous Message | markw | 2003-12-12 23:32:29 | Re: more dbt-2 results hyperthreading on linux-2.6.0-test11 |