X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9002014B286C for ; Mon, 9 Aug 2004 11:53:53 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44498-10 for ; Mon, 9 Aug 2004 14:53:44 +0000 (GMT) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by svr1.postgresql.org (Postfix) with SMTP id BC43914B286B for ; Mon, 9 Aug 2004 11:53:41 -0300 (ADT) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Mon Aug 09 16:54:30 2004 +0200 Received: from rodos.fzk.de (resy6.fzk.de [141.52.68.10]) by resy5.fzk.de (8.12.7/8.12.7/SuSE Linux 0.6) with ESMTP id i79ErgsK001378; Mon, 9 Aug 2004 16:53:42 +0200 Message-ID: <41178FF4.729EAB36@rodos.fzk.de> Date: Mon, 09 Aug 2004 16:53:41 +0200 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: k.p.lehre@basalmed.uio.no Cc: pgsql-sql@postgresql.org Subject: Re: select References: <40FEE9C6000006B4@cpfe9.be.tisc.dk> Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS - amavis-milter (http://www.amavis.org/) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=HOT_NASTY X-Spam-Level: X-Archive-Number: 200408/60 X-Sequence-Number: 18557 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