MySQL: order by inside group by

Question

I have a mysql table like this:

mysql> select * from pt_onhand where pn = '000A569011';
+------------+-----+----+--------+------------+---------+--------------+-----+
| pn         | pa  | mn | ACTIVE | locate     | onhand  | avg_cost     | whs |
+------------+-----+----+--------+------------+---------+--------------+-----+
| 000A569011 | P/A |    |        | AA-112     | 13.0000 | 0.0000000000|     |
| 000A569011 | P/A |    |        | PF120136.1 |  1.0000 | 5.4785156200 |     |
+------------+-----+----+--------+------------+---------+--------------+-----+

and I want to execute a query like this:

mysql> select sum(onhand),max(locate),avg_cost from pt_onhand where pn = '000A569011' group by pn;
+-------------+-------------+--------------+
| sum(onhand) | max(locate) | avg_cost     |
+-------------+-------------+--------------+
|     14.0000 | PF120136.1  | 0.0000000000|
+-------------+-------------+--------------+

so my question is this:can I get the avg_cost 5.4785156200 which related to the max(locate) PF120136.1 in the same query,how? thanks

Answer 1

It's a little bit scrappy, but it should do the trick:

select a.onhand, a.locate, p.avg_cost
from
    (select sum(onhand) onhand, max(locate) locate from pt_onhand where pn = '000A569011' group by pn) a
    join pt_onhand p on p.locate = a.locate

Answer 2

You could also do a sub query as:

select 
     sum(onhand)
    ,max(locate)
    ,(select avg_cost from pt_onhand where pn = pt.pn and locate = max(pt.locate)) as avg_cost 
from 
    pt_onhand pt 
where 
    pn = '000A569011' 
group by pn;

But may not perform very well depending on how big your db is, try them all out, see which works best for you