MySQL: order within a group

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 avg_cost 5.4785156200, which is associated with max (locate) PF120136.1 in the same query as? thanks

Answer 1

This is a little patchwork, but he 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 can also run an additional request:

 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 it may not work very well depending on how big your db is, try all of them, see which one is best for you.