You can achieve this with a subselect, and then you join the results whith the query you already have:
SELECT T.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS max_cmup, sum(T.qty) AS sum_qty
FROM my_table T, (SELECT partno, max(cmup) AS max_cmup_for_partno FROM my_table GROUP BY partno) AS TMP
WHERE tmp.partno=T.partno
GROUP BY T.partno, TMP.max_cmup_for_partno, T.status
Hope it helped.
On Thu, 2004-11-04 at 13:54, Michael L. Hostbaek wrote:
List, I've got a table looking something like this: my_table some_id int bla bla, partno varchar(100), status varchar(100), cmup numeric(14,2), qty int Here a small sample of contents in my table: some_id partno status cmup qty 1 test1 stock 10.00 15 2 test2 incoming 12.00 10 3 test1 incoming 15.00 60 4 test1 incoming 14.00 11 My SQL select statement will then group together partno, status and aggregate sum(qty) and max(cmup). This is all good and nice. My result will look something like this: partno status cmup qty test1 stock 10.00 15 test1 incoming 15.00 71 test2 incoming 12.00 10 Now, I need the first line to say "15.00" in the cmup field. That is, stock and incoming are obviously not being grouped, but since it's the same partno I'd like somehow to show the highest cmup. Is there some black SQL voodoo that'll achieve this ? TIA,
signature.asc
Description: This is a digitally signed message part
