On Thu, 2004-11-04 at 16:54, Michael L. Hostbaek wrote: ... > 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 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 ?
junk=# select partno, status, (select max(cmup) from my_table as b where b.partno = a.partno) as cmup, sum(qty) from my_table as a group by partno, status, (select max(cmup) from my_table as b where b.partno = a.partno); partno | status | cmup | sum --------+----------+-------+----- test1 | incoming | 15.00 | 71 test1 | stock | 15.00 | 15 test2 | incoming | 12.00 | 10 (3 rows) Oliver Elphick ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
