Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id 8341C475A78 for ; Thu, 21 Feb 2002 16:45:19 -0500 (EST) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id QAA06644 for ; Thu, 21 Feb 2002 16:45:21 -0500 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma006642; Thu, 21 Feb 02 16:45:17 -0500 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id QAA24073 for ; Thu, 21 Feb 2002 16:46:44 -0500 Message-ID: <3C756CB8.74537FBC@nsd.ca> Date: Thu, 21 Feb 2002 16:55:04 -0500 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-12 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: maximum of sums References: <3c71e972$0$16639$afc38c87@news.optusnet.com.au> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/293 X-Sequence-Number: 6716 Rob, How about select prod_no, sum( quantity) from orders group by prod_no order by 2 desc limit 1; JLL Rob wrote: > > Howdy all! > > Let's say we have a product orders table like this: > > SQL> select * from orders; > > ORDER_NO PROD_NO QUANTITY > --------- --------- --------- > 1 2 3 > 2 2 3 > 3 3 3 > 4 1 4 > 5 3 8 > 6 2 7 > 7 1 6 > 8 1 3 > 9 3 3 > 10 2 4 > > 10 rows selected. > > SQL> > > I want to select the prod_no and sum (quantity) for the product with the max sum > (quantity). > > I have this so far: > SQL> select > 2 max (sumamt) as maximum > 3 from > 4 (select > 5 sum (orders.quantity) as sumamt > 6 from > 7 orders > 8 group by > 9 orders.prod_no); > > MAXIMUM > --------- > 17 > > SQL> > > But how can I get the matching prod_id? > > Any help would be much appreciated! > > Rob > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly