From: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de> |
---|---|
To: | k(dot)p(dot)lehre(at)basalmed(dot)uio(dot)no |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: select |
Date: | 2004-08-09 14:53:41 |
Message-ID: | 41178FF4.729EAB36@rodos.fzk.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Knut P Lehre wrote:
> I have a table with fields:
> id A B C D E F
> where id is an int4 primary key.
> In this table there is information like:
> 1 a1 b1 xxx xxx
> 2 a1 b1 xxx xxx xxx xxx
> 3 a2 b2 xxx xxx xxx xxx
> 4 a2 b2 xxx xxx xxx xxx
> 5 a3 b3 xxx xxx xxx
> 6 a3 b3 xxx xxx xxx xxx
> 7 a3 b3 xxx xxx xxx xxx
> 8 a4 b4 xxx xxx xxx xxx
> 9 a1 b1 xxx
> 10 a3 b3 xxx
> 11 a1 b3 xxx
> where xxx represents any information.
> My problem is: I only want to select one of the records which have the same
> combination of information in fields A and B, and that should be the record
> with the lowest id. Thus, the resultset should be:
> 1 a1 b1 xxx xxx
> 3 a2 b2 xxx xxx xxx xxx
> 5 a3 b3 xxx xxx xxx
> 8 a4 b4 xxx xxx xxx xxx
> 11 a1 b3 xxx
> Importantly, I might not want the selected records listed in order of increasing
> id. They might be sorted according to the data in e.g. the C field.
>
> Suggestions/comments greatly appreciated.
>
Does this give you the result you are looking for?
SELECT * FROM (
SELECT DISTINCT ON (A,B) * FROM table ORDER BY id
) AS foo ORDER BY C ;
Regards, Christoph
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Nuzum | 2004-08-09 14:56:21 | Suggestions on storing re-occurring calendar events |
Previous Message | Sascha Ziemann | 2004-08-09 09:41:49 | Re: UNIQUE constraint |