ORDER BY and DISTINCT ON

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

Responses

Browse pgsql-hackers by date

  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