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
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 ?
You *CAN* sort by aggregates
e.g.
select partno, status, sum(cmup) as cmup, sum(qty) as qty
from my_table
group by partno, status
order by partno, sum(cmup) desc;
partno | status | cmup | qty
--------+----------+------+-----
test1 | incoming | 29 | 71
test1 | stock | 10 | 15
test2 | incoming | 12 | 10
--
Edmund Bacon <[EMAIL PROTECTED]>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
