SQL statement?

Question

Lets say I have 2 tables one called EMP_INFO and another called ACTIVITY linked one to many by EMP_INFO.EMP_ID to Activity.EMP_ID. I want to return all the employees with there info and the last time they logged on. Assuming that I want to pull all the empoyees info and only the greatest value of logged_on for each employee how can this been done. I triend
"select emp_info.*, max(activity.logged_on) from emp_info join activity on emp_info.emp_id = activity.emp_id" but that is not accepted because max(activity.logged_on) is just wanting to pull the largest value on logged_on and not the largest value for logged_on for each employee in emp_info. How can this be re-wrote to work?

Answer 1

there are various way to do this, I prefer this :
you can use: "Group By"
SELECT emp_info.EMP_ID, emp_info.EMP_NAME, max(activity.logged_on)
FROM emp_info, activity
WHERE emp_info.EMP_ID = activity.EMP_ID
GROUP BY emp_info.EMP_ID, emp_info.EMP_NAME,

be careful, not use * --> "select emp_info.*"
use field name --> "select emp_info.EMP_ID, emp_info.EMP_NAME"

Answer 2

You have to use a GROUP BY.

Try something like

SELECT a.emp_id, max(b.logged_on)
FROM emp_info a, activity b
where a.emp_id = b.emp_id
group by a.emp_id;

Remember though, using a group by, you can only select columns that are used in the group by clause or that you reference with a group function like max.