Re: Group by and aggregates

If I understand well, you want the highest cmup for each partno, that is max(cmup) grouped by partno (only). <br /><br
/>You can achieve this with a subselect, and then you join the results whith the query you already have:<br /><br />
SELECTT.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS max_cmup, sum(T.qty) AS sum_qty<br /> FROM my_table
T,(SELECT partno, max(cmup) AS max_cmup_for_partno FROM my_table GROUP BY partno) AS TMP<br /> WHERE
tmp.partno=T.partno<br/> GROUP BY T.partno, TMP.max_cmup_for_partno, T.status<br /><br /> Hope it helped.<br /><br
/><br/> On Thu, 2004-11-04 at 13:54, Michael L. Hostbaek wrote: <blockquote type="CITE"><pre><font
color="#737373"><i>List, 

I've got a table looking something like this:

my_tablesome_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, </i></font></pre></blockquote>