[Dbix-class] Using 'DISTINCT ON' clause in select statement

[Stephan Szabo]

On Fri, 18 Aug 2006, Brandon Black wrote:

> On 8/18/06, Paul Steinkamp <steinkam at apple.com> wrote:
> >
> > Greetings,
> >
> > I'm using postgres as my DB which will accept the clause:
> >
> >         SELECT DISTINCT ON ( field1, ..., fieldn)    fieldA, fieldB,...
> > FROM ....
> >
> > Note there is no comma after the closing ')' after the 'DISTINCT ON'
> > clause. My various attempts either end up not getting it included at
> > all or included as a function (and therefore separated from the
> > selection field lists by a comma).
> >
> > Is there a way within the class (short of raw SQL) to perform this
> > kind of select?
>
>
> Can't the same effect be achieved with GROUP BY?

Not generally if fieldA, fieldB, etc aren't in the grouping (or in this
case distincted) columns.

If I remember correctly, if the grouping columns are a key (or a superset
thereof), then SQL (as of SQL99) allows you to get direct column
references that aren't in the grouping columns or a set function, but
otherwise you cannot, presumably because there isn't a single value
guaranteed for the group. PostgreSQL doesn't yet implement the first part
where you can use only the key columns.