Re: ORDER BY and DISTINCT ON

Greg Stark <gsstark@mit.edu> writes:
> Do you really want:
>
> select distinct on (b,c,a) a,b,c from abc order by b,c,a;
>
> or is that you want
>
> select * from (select distinct on (a) a,b,c order by a) order by
> b,c,a;

If I understand you correctly, I don't think I would expect either.
  - ORDER BY provides a sort order for the result set
  - DISTINCT ON specifies a list of expressions, and says: "For each    set of rows in the result set for which these
expressionsare all    equal, retain the first row and throw the rest away", where the    "first row" is defined by the
ORDERBY sort order
 

So I'd expect this query to
  (a) keep at most one of every distinct 'a' value. When throwing out      duplicates, we should keep the row that
wouldcome first as      specified by the ORDER BY sort order
 
  (b) sort the result set by b,c,a

ISTM this interpretation is pretty logical, and that the current
restriction is made purely for the sake of ease-of-implementation. If
that's the case, we should at least document this restriction, and
perhaps plan on correcting it in the future.

-Neil