Re: DISTINCT ... ORDER BY

On Wed, 5 Nov 2003, Nabil Sayegh wrote:

> Stephan Szabo wrote:
>
> >>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?
>
> I want it to just return 1 column ;)
>
> > 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?
>
> It shall not mention col2 at all.
> The idea is to
> 1. ORDER BY expressions
> 2. Pick only 1 column
> 3. make this column distinct without losing the sort order

You can either use something like the distinct on extension with
subselects or some variation on group by.  Both of these involve
understanding however, which row you want out when you're going to
be dropping rows.

Your step 3 above isn't well defined in general.  There's no single place
in the sort order for a value of a column being distincted when the value
occurs multiple times.  In the data above, col1='b' occurs twice in the
sort order and you need to give an indication of which place in the sort
order you want to use.