Re: DISTINCT ... ORDER BY

On Wed, Nov 05, 2003 at 17:52:03 +0100,
Nabil Sayegh <postgresql(at)e-trolley(dot)de> wrote:
> Bruno Wolff III wrote:
>
> >You can use a distinct on with order by clause as a subselect to eliminate
> >the records you don't want. Then you can extract col3 from this subselect
> >and order by the original sort order (instead of col3 first) to get the
> >desired records in the desired order.
> >
> >It would look something like:
> >select col3 from
> > (select distinct on (col3) * from tablename order by col1, col2, col3,
> > col4)
> > order by col1, col2, col3, col4;
>
> Unfortunately I need to ORDER BY expression, not plain columns.
>
> SELECT DISTINCT ON (col_1) col_1 FROM tab ORDER BY col_2='foo' DESC,
> col_1='bar' DESC, col_3='blah' DESC;
>
> fails with:
>
> ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
> expressions

I made a mistake in my example. In the distinct on subselect you have to
order by the column(s) in the distinct on argument first. So in your example
above the subselect should be:
SELECT DISTINCT ON (col_1) col_1 FROM tab ORDER BY col_1, col_2='foo' DESC,
col_1='bar' DESC, col_3='blah' DESC;

In the outer select you use just the order by items that you want. Also
note that the subselect will need to list all of the columns used in the
order by in the outer select.