add array arg to select().distinct(), query().distinct()

[Mike Bayer]

this is to fully support PG's DISTINCT ON, as in http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-DISTINCT . add unit tests.

We cannot use the standalone distinct() for this since it is not a separate column expression.

[Former user]

SQL for DISTINCT ON seem to be in postgresql dialect adapter already for 0.5.5/0.6, so it seems just a matter of adding a few lines to Query.distinct.

Just found myself in need of this feature, so wrote a simple implementation for query class.[BR] Prehaps someone might find this patch useful, at least while it's not implemented in trunk.

[Former user]

Wow, three years and “awaiting triage”...

To underline the severity here, I show how a decent group_by is affected:

SELECT a, b, c FROM x GROUP BY a

gets an error with PostgreSQL, because it doesn’t like to have b and c outside of aggregate functions. A DISTINCT ON a should be used in stead, which gives us any random b and c that fits -- that’s what I want.

[Mike Bayer]

Replying to guest:

Wow, three years and “awaiting triage”...

To underline the severity here, I show how a decent group_by is affected:

SELECT a, b, c FROM x GROUP BY a

gets an error with PostgreSQL, because it doesn’t like to have b and c outside of aggregate functions. A DISTINCT ON a should be used in stead, which gives us any random b and c that fits -- that’s what I want.

GROUP BY is mostly to do with grouping so that aggregates can be applied. The above query can link the selection of 'b' and 'c' to that of 'a' via a subquery, which is probably why I never find myself needing "DISTINCT ON".

[Mike Bayer]

note a more complete patch has been attached but still needs tests.

[Mike Bayer]

3f9a343d725eea883aba2145de4cbb7b84f9d08c

[Mike Bayer]

Removing milestone: 0.7.0 (automated comment)