Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1J9s4E01970 for ; Tue, 19 Feb 2002 04:54:04 -0500 (EST) (envelope-from news@jupiter.hub.org) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g1J9kSu78567 for pgsql-sql@postgresql.org; Tue, 19 Feb 2002 03:46:28 -0600 (CST) (envelope-from news) From: ThomasR X-Newsgroups: comp.databases.postgresql.sql Subject: Re: maximum of sums Date: Tue, 19 Feb 2002 10:58:55 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 43 Message-ID: <3C7221DF.9060907@wtal.de> References: <3c71e972$0$16639$afc38c87@news.optusnet.com.au> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: news@hub.org User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.8) Gecko/20020204 X-Accept-Language: en-us, de To: pgsql-sql@postgresql.org X-Archive-Number: 200202/226 X-Sequence-Number: 6650 Rob wrote: > Howdy all! > [snap table] > > 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? perhaps with the limit-clause: select prod_no, sum( quantity ) as sumq from orders group by prod_no order by sumq desc limit 1 But it fails, if you have two or more products with the same amount of orders Thomas