From jkanter@virginia.edu Wed Dec 10 20:25:35 2003 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 5AAADD1B498 for ; Wed, 10 Dec 2003 20:25:35 +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 15708-09 for ; Wed, 10 Dec 2003 16:25:05 -0400 (AST) Received: from unix.mail.Virginia.EDU (unix.mail.Virginia.EDU [128.143.2.28]) by svr1.postgresql.org (Postfix) with ESMTP id AA82DD1B468 for ; Wed, 10 Dec 2003 16:25:02 -0400 (AST) Received: from smtp.mail.Virginia.EDU (smtp.mail.Virginia.EDU [128.143.22.52]) by unix.mail.Virginia.EDU (Postfix) with ESMTP id 410F7A8AF for ; Wed, 10 Dec 2003 15:24:45 -0500 (EST) Received: from virginia.edu (d-137-133-128.bootp.Virginia.EDU [137.54.133.128]) by smtp.mail.Virginia.EDU (Postfix) with ESMTP id B476121042 for ; Wed, 10 Dec 2003 15:24:44 -0500 (EST) Message-ID: <3FD783F6.6030801@virginia.edu> Date: Wed, 10 Dec 2003 15:37:10 -0500 From: Jodi Kanter User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Postgres SQL List Subject: picking max from list Content-Type: multipart/alternative; boundary="------------010705030006050506040000" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/86 X-Sequence-Number: 16252 This is a multi-part message in MIME format. --------------010705030006050506040000 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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 Jodi -- /_______________________________ //Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 jkanter@virginia.edu / / / / / --------------010705030006050506040000 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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
Jodi

--
________________________________

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu

--------------010705030006050506040000-- From bruno@wolff.to Wed Dec 10 20:54:37 2003 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; From guy@incentre.net Wed Dec 10 23:11:55 2003 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 D3A15D1B4AE for ; Wed, 10 Dec 2003 23:11:55 +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 42505-04 for ; Wed, 10 Dec 2003 19:11:27 -0400 (AST) Received: from bach.ccinet.ab.ca (bach.ccinet.ab.ca [198.161.96.1]) by svr1.postgresql.org (Postfix) with ESMTP id 46674D1B4B3 for ; Wed, 10 Dec 2003 19:11:24 -0400 (AST) Received: from incentre.net (guy-pc.incentre.net [206.75.213.195] (may be forged)) by bach.ccinet.ab.ca (8.12.9p2/8.12.8) with ESMTP id hBANB3OV053396; Wed, 10 Dec 2003 16:11:04 -0700 (MST) (envelope-from guy@incentre.net) Message-ID: <3FD7A80E.4000208@incentre.net> Date: Wed, 10 Dec 2003 16:11:10 -0700 From: Guy Fraser Organization: The Internet Centre User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20030225 X-Accept-Language: en-ca, en-gb, en-us, en MIME-Version: 1.0 To: Jodi Kanter Cc: Postgres SQL List Subject: Re: picking max from list References: <3FD783F6.6030801@virginia.edu> In-Reply-To: <3FD783F6.6030801@virginia.edu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/88 X-Sequence-Number: 16254 select group,rd_pk from (select ...) as your_query, (select max(run) as max_run,rd_pk as rd from (select ...) as your_query) as max_rd where rd = rd_pk and max_run = run; I dont know if you call that one query but it should work. There may be more elegant solutions, but I havn't had a chance to read up on the new join types. Good luck 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 > Jodi -- Guy Fraser Network Administrator