From: Rob <"r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores"@news.tht.net>
To: pgsql-sql@postgresql.org
Subject: maximum of sums
Date: Tue, 19 Feb 2002 16:54:08 +1100
Message-ID: <3c71e972$0$16639$afc38c87@news.optusnet.com.au> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: "r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores"@news.tht.net
Subject: Re: maximum of sums
In-Reply-To: <3c71e972$0$16639$afc38c87@news.optusnet.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox