axiom82 Posted February 27, 2008 Share Posted February 27, 2008 I have attached a screenshot to this message to help describe my issue exactly. I have two tables: employee and employee_timestamp. I need to JOIN them together and GROUP BY the employee_id field which I have successfully done. The next step is the one that is the problem. When I do the GROUP BY, I want to retrieve the LATEST employee_timestamp record. Here is the query (not working) and I've attached a screenshot to describe the issue. SELECT e.id, et.date, max(et.date) AS max_date FROM employee e LEFT JOIN (employee_timestamp et) ON (et.id = e.id) GROUP BY e.id HAVING max_date; You can see on the screenshot that the two date columns do not match...which means the latest timestamp was not retrieved as the "joined timestamp row". It's important that I have that row as it has other important information in it. Please help Quote Link to comment Share on other sites More sharing options...
aschk Posted February 27, 2008 Share Posted February 27, 2008 So you want the latest timestamp for each employee? SELECT e.id, x.max_date FROM employee e JOIN ( SELECT id, MAX(date) as 'max_date' FROM employee_timestamp et GROUP BY id ) x ON x.id = e.id; Just taking a look over your info where you say the latest timestamp was not retrieved as the "joined timestamp row" I think that statement is wrong. Because you're not specified the date in your GROUP BY, MySQL can use a random row for the date column you have selected. So your method of "matching up" is incorrect I believe. Quote Link to comment Share on other sites More sharing options...
axiom82 Posted February 27, 2008 Author Share Posted February 27, 2008 Yes. Thank you! This works perfectly...now I can move forward Quote Link to comment Share on other sites More sharing options...
axiom82 Posted February 27, 2008 Author Share Posted February 27, 2008 That's getting closer but not entirely right. The objective is to return not only the max_date FROM the employee_timestamp table, but to return the entire row where the max_date value exists. This is more along the lines of what I want to accomplish...but it of course doesn't work as expected. SELECT e.id, et.event, et.max_date FROM employee e JOIN ( SELECT employee_id, event, date FROM employee_timestamp et ORDER BY date DESC LIMIT 1 ) x ON x.employee_id = e.id Quote Link to comment Share on other sites More sharing options...
axiom82 Posted February 27, 2008 Author Share Posted February 27, 2008 This is working perfectly...but damn it's a lot of code for an SQL query LOL... SELECT e.id, et2.event, et2.date FROM employee e JOIN ( SELECT employee_id, max(date) as max_date FROM employee_timestamp GROUP BY employee_id ) et ON et.employee_id = e.id JOIN ( SELECT employee_id, event, date FROM employee_timestamp ) et2 ON et2.employee_id = e.id AND et2.date=et.max_date ORDER BY e.id Quote Link to comment Share on other sites More sharing options...
acidglitter Posted February 27, 2008 Share Posted February 27, 2008 i've never even heard of left join (theres left OuteR join..)... i think it would work if you used your original code, but changed left to inner.. SELECT e.id, et.date, max(et.date) AS max_date FROM employee e INNER JOIN (employee_timestamp et) ON (et.id = e.id) GROUP BY e.id HAVING max_date; Quote Link to comment Share on other sites More sharing options...
aschk Posted February 28, 2008 Share Posted February 28, 2008 Ah i see what you're after now. You want the event that corresponds to the max_date from the employee_timestamp table. Try: SELECT e.id, x.event, x.max_date FROM employee e JOIN ( SELECT id, event, MAX(date) as 'max_date' FROM employee_timestamp et GROUP BY id, event HAVING `date` = max_date ) x ON x.id = e.id; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.