mySQL - GROUP BY but get the most recent row

Question

I've got a budget table:

user_id     product_id   budget      created 
-----------------------------------------------------------------
   1             1        300      2011-12-01
   2             1        400      2011-12-01
   1             1        500      2011-12-03
   2             2        400      2011-12-04

I've also got a manager_user table, joining a manager with the user

 user_id    manager_id    product_id
 ------------------------------------
    1           5             1
    1           9             2
    2           5             1
    2           5             2
    3           5             1

What I'd like to do is grab each of the user that's assigned to Manager #5, and also get their 'budgets'... but only the most recent one.

Right now my statement looks like this:

SELECT * FROM manager_user mu 
LEFT JOIN budget b 
ON b.user_id = mu.user_id AND b.product_id = mu.product_id 
WHERE mu.manager_id = 5
GROUP BY mu.user_id, mu.product_id
ORDER BY b.created DESC;

The problem is it doesn't pull the most recent budget. Any suggestions? Thanks!

I've got a working (though not perfect) query that says otherwise: http://i.imgur.com/M325Z.jpg — TerryMatula, Dec 13 '11 at 23:23
I posted a solution, and saw that your query works in mysql, but read this too: http://stackoverflow.com/questions/1225144/why-does-mysql-allow-group-by-queries-without-aggregate-functions. — Andrea Colleoni, Dec 13 '11 at 23:56

Andrea Colleoni · Answer 1 · 2011-12-14T19:52:08.840

0

To accomplish your task you can do as follows:

select b1.user_id, 
       b1.budget
from budget b1 inner join (
  select b.user_id, 
         b.product_id, 
         max(created) lastdate
  from budget b
  group by b.user_id, b.product_id ) q
on b1.user_id=q.user_id and 
   b1.product_id=q.product_id and 
   b1.created=q.lastdate
where b1.user_id in 
  (select user_id from manager_user where manager_id = 5);

I'm assuming here that your (user_id, product_id, created) combination is unique.

edited Dec 14 '11 at 19:52

answered Dec 13 '11 at 23:51

Andrea Colleoni

5,919
3
30
49

Thanks. I changed "group by b.user_id, b.budget" to "GROUP BY b.user_id, b.product_id" and got it working... EXCEPT that I also need to return the user_ids for those that aren't in the budget table. I'll try to play around with it and see what I come up with. – TerryMatula Dec 14 '11 at 15:10
You're right; I edited my answer. To have all user_id's you can outer join manager_user table instead of using IN clause. – Andrea Colleoni Dec 14 '11 at 19:53

score 0 · Answer 2 · answered Dec 14 '11 at 22:17

For what it's worth, here's the code that returned what I was looking for:

SELECT DISTINCT(b1.id),mu.user_id,mu.product_id,b1.budget,b1.created
FROM budget b1
INNER JOIN (
   SELECT b.user_id, b.product_id, MAX(created) lastdate
   FROM budget b
   GROUP BY b.user_id, b.product_id) q 
ON b1.user_id=q.user_id AND 
  b1.product_id=q.product_id AND 
  b1.created=q.lastdate
RIGHT JOIN manager_user mu 
    ON mu.user_id = b1.user_id AND 
    mu.product_id = b1.product_id
WHERE mu.manager_id = 5;

Thanks for the help Andrea!

mySQL - GROUP BY but get the most recent row

2 Answers2