Skip site navigation (1) Skip section navigation (2)

Re: DISTINCT ... ORDER BY

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Nabil Sayegh <postgresql(at)e-trolley(dot)de>
Cc: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: DISTINCT ... ORDER BY
Date: 2003-11-05 04:23:48
Message-ID: 20031105042348.GA29909@wolff.to (view raw or flat)
Thread:
Lists: pgsql-novice
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;

In response to

Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2003-11-05 05:18:27
Subject: Re: DISTINCT ... ORDER BY
Previous:From: Farid KhanDate: 2003-11-05 04:05:49
Subject: Primary Keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group