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

DISTINCT ... ORDER BY

From: Nabil Sayegh <postgresql(at)e-trolley(dot)de>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: DISTINCT ... ORDER BY
Date: 2003-11-05 00:48:20
Message-ID: 3FA848D4.3070202@e-trolley.de (view raw or flat)
Thread:
Lists: pgsql-novice
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};

TIA
-- 
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de


Responses

pgsql-novice by date

Next:From: Farid KhanDate: 2003-11-05 04:05:49
Subject: Primary Keys
Previous:From: Josh BerkusDate: 2003-11-04 22:00:05
Subject: Re: Surrogate vs natural keys (Was: Almost relational PostgreSQL (was: one-to-one))

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