ENH: Postgres: Provide API for SELECT DISTINCT ON (<column>, <column>, ...)

Provide API to support Postgres query extension SELECT DISTINCT ON (..)

SELECT DISTINCT ON (c.id) 
  c.id, e.date
FROM city c JOIN events e ON c.id = e.city_id
WHERE c.id IN (....)
ORDER BY c.id, e.date DESC

These type of queries run usually a lot faster than the common e.date = (SELECT max(e2.date) ...) subquery solution to find the latest event date per city.

Proposed API

Similar do setDistinct(boolean) Ebean could provide setDistinctOn(<column alias>...), e.g.

var city = QCity.alias();
query.setDistinctOn(city.id) // varargs parameter to support more columns

I don't know if Ebean does query checking, but if it does, then it would be worth a warning if both conditions below are not true for such a query. Otherwise the query result will be unpredictable.

1. columns in DISTINCT ON should match left most columns in ORDER BY
2. number of columns in ORDER BY > number of columns in DISTINCT ON

ebean-orm-deprecated / ebean-querybean

ENH: Postgres: Provide API for SELECT DISTINCT ON (<column>, <column>, ...) #70

Proposed API