subquery ORDER BY GROUP BY

[smerny]

i have a table with employees which includes their ID and their managers IDand a table with performance_reviews which includes rating, date_of_pr, and employee_ID as FKI want a query that returns the MOST RECENT date_of_pr for every employee who has a certain manager (manager_ID)i don't understand why:

"SELECT rating FROM performance_review WHERE employee_ID IN (	SELECT employee_ID FROM employee WHERE manager_ID=".$m_id.")	ORDER BY date_of_pr DESC	GROUP BY employee_ID";

isn't working for me..1) subquery should get a list of employee_ID's of employees who have the correct manager2) selecting from performance_review table where employee_ID is IN that list should return all the records for every review all the employees who's ID shows in that list have ever had3) ordering all those records by date descending would put those records in order starting with most recent date4) grouping those by employee ID should make it only return the first value for each employee ID? (which - after sorting - would be the most recent)help please

[xjx424]

try putting your GROUP BY before your ORDER BY

[smerny]

try putting your GROUP BY before your ORDER BY

well, that would get rid of the syntax error, but there would be a logical error.it would:1) subquery should get a list of employee_ID's of employees who have the correct manager2) selecting from performance_review table where employee_ID is IN that list should return all the records for every review all the employees who's ID shows in that list have ever had3) group all that by employee_ID which would only return the first record in the database for each employee rather than the record with the most recent date4) ordering all those records by date descending would just show order the records of the first instance of each employee

[xjx424]

Because you are using "SELECT rating" you will only return the rating, not the date.If you only want the most recent review for each employee for a particular manager, try

"SELECT *, MAX(date_of_pr) as mostRecent FROM performance_review WHERE employee_ID IN (	SELECT employee_ID FROM employee WHERE manager_ID=".$m_id.")	GROUP BY employee_ID";	ORDER BY date_of_pr DESC

[smerny]

what that does is return the first instance of each employee_ID in the table along with the most recent date_of_pr stuck on the end.... and then sorted by date_of_pr which is just the date of the first instance of each employee_IDit does not return the rating associated with the most recent date_of_pr for each employee.

[smerny]

got it working now, with this:

SELECT rating, date_of_pr, pr_ID FROM performance_review pr RIGHT OUTER JOIN employee e ON pr.employee_ID = e.employee_id WHERE pr.date_of_pr = (SELECT MAX(date_of_pr) 	FROM performance_review 	WHERE employee_ID = pr.employee_ID) 	AND  e.manager_id='".$m_id."'