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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
