Re: DISTINCT ... ORDER BY

On Wed, Nov 05, 2003 at 01:48:20 +0100,
Nabil Sayegh <postgresql(at)e-trolley(dot)de> wrote:
> Hi all,
>
> I have built a query that mainly orders all columns.
> It shouldn't matter how it orders but FYI:
> I need to order by 1 given column and then by all others from 'left to
> right'.
>
> No problem so far.
>
> Then I want to return only 1 column (e.g. the 3rd) with duplicates removed
> _AND_ still in the same order like above.
>
> e.g.:
>
> myCol
> -----
> a
> a
> c
> b
> b
> c
> b
>
> should return:
>
> myDistinctOrderedCol
> --------------------
> a
> c
> b
>
> The problem is that DISTINCT complains about ORDER BY entries not being in
> the target list.
> Any idea ?
>
> Example:
>
> initial query:
> SELECT * FROM tab ORDER BY col_2='foo' DESC, col_1='bar' DESC, col_3='blah'
> DESC;
>
> Then something like:
> SELECT DISTINCT col3 FROM {initial query};

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;