X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B879DD1B498 for ; Wed, 10 Dec 2003 20:54:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 21707-02 for ; Wed, 10 Dec 2003 16:54:07 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 490A3D1B484 for ; Wed, 10 Dec 2003 16:54:05 -0400 (AST) Received: (qmail 19186 invoked by uid 500); 10 Dec 2003 20:53:40 -0000 Date: Wed, 10 Dec 2003 14:53:40 -0600 From: Bruno Wolff III To: Jodi Kanter Cc: Postgres SQL List Subject: Re: picking max from list Message-ID: <20031210205340.GA18726@wolff.to> Mail-Followup-To: Jodi Kanter , Postgres SQL List References: <3FD783F6.6030801@virginia.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3FD783F6.6030801@virginia.edu> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/87 X-Sequence-Number: 16253 On Wed, Dec 10, 2003 at 15:37:10 -0500, Jodi Kanter wrote: > I have a query that produces results similar to this: > > run# rd_pk group# > 0 9209 5 > 1 9209 8 > 0 9520 2 > 1 9520 5 > 0 9520 etc.... > 0 8652 > 1 8652 > 2 8652 > 0 8895 > 1 8894 > > Ultimately I want to know the group number for EACH rd_pk with the > highest run number. Can this be done in one query? Or will I need to > code with a loop? > Thanks You can do this using the postgres distinct on extension. The query would look something like: select distinct on (rd_pk) run, rd_pk, group from table_name order by run desc;