Sub-query GROUP BY ORDER BY

[smerny]

tried this:

SELECT rating FROM performance_review pr, employee e, 
   (SELECT max(date_of_pr) AS recent, pr_ID FROM performance_review
    GROUP BY employee_ID) AS pr2
WHERE e.manager_ID = '".$m_id."'
AND e.employee_ID = pr.employee_ID
AND pr.pr_ID = pr2.pr_ID

 

but it wasn't giving me the most recent rating.... so i just ran the SQL for the subquery

SELECT max(date_of_pr) AS recent, pr_ID FROM performance_review
    GROUP BY employee_ID

and got

 

recentpr_ID

2010-07-176

2010-06-238

 

from this table

 

pr_ID employee_ID reviewer_ID review_type date_of_pr rating

6  2 1 normal 2009-07-19 5

5 2 1 normal 2010-07-17 4

7 2 1 normal  2010-02-01 4

8 11 9 progress  2009-06-08 5

9 11 9 normal  2009-12-15 3

10 11 9 normal 2010-06-23 4

 

it should only get records with pr_ID 5 and 10 (most recent date_of_pr per employee_ID)

[Philip]

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."

[smerny]

that seems to be working. thanks philip.