Received: from news.tht.net (news.hub.org [216.126.91.242] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1J60rE47040 for ; Tue, 19 Feb 2002 01:00:54 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g1J609W35577 for pgsql-sql@postgresql.org; Tue, 19 Feb 2002 01:00:09 -0500 (EST) (envelope-from news) From: "Rob" <"r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores"@news.tht.net> X-Newsgroups: comp.databases.postgresql.sql Subject: maximum of sums Date: Tue, 19 Feb 2002 16:54:08 +1100 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Lines: 50 Message-ID: <3c71e972$0$16639$afc38c87@news.optusnet.com.au> To: pgsql-sql@postgresql.org X-Archive-Number: 200202/283 X-Sequence-Number: 6706 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