Re: DISTINCT ... ORDER BY

On Wed, 5 Nov 2003, Nabil Sayegh wrote:

> 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.

Right, because given this data:
col1 | col2
 a   |  b
 b   |  c
 b   |  a

what ordering should
 select distinct col1 from tab order by col2
give you?

Does it put b first because there's a col2 with a, or b second because
there's a col2 with b or is it indeterminate?


Maybe something of this general form would work if you want to order by
the minimums:
select col3 from tablename group by col3 order by min(col1), min(col2),
col3, min(col4);