Sub-query GROUP BY ORDER BY

[smerny]

SELECT rating 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

this is the code i came up with that isnt working for me.

 

performance_review table may have several ratings for each employee.

 

employee table has many employees

 

I want to create a list of the MOST RECENT performance_review.rating for each employee where that employee has a specific manager_ID in the employee table.

 

for example, if employees 5,9,11 are managed by manager 5... and the MOST RECENT rating for employee 5 is "4", for employee 9 is "6", and employee 11 is "3"... the query should bring back the records 4,6,3 when $m_id is "5"

 

thanks for any help

 

 

[premiso]

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

 

If you want to use the GROUP BY you have to select the employee_ID as well, but since you just want the latest, there is no need to group by employee_id, if you wanted to do a sum of all their ratings that would be different.

[smerny]

wouldn't your example return all ratings from every record for each employee_ID rather than just the most recent?

 

the reason i used GROUP BY is so it would only return one per employee_ID rather than all matching records

[fenway]

This is a common question.

 

First, write a query to get the most recent for each employee -- then, join this derived table back to the original table, and voila.

[smerny]

This is a common question.

 

First, write a query to get the most recent for each employee -- then, join this derived table back to the original table, and voila.

 

1)

"SELECT rating FROM performance_rating ORDER BY date_of_pr GROUP BY employee_ID

2)

JOIN (SELECT employee_ID FROM employee WHERE manager_ID='".$m_id."') ON employee_ID"

 

thats my attempt at implementing what you just said

 

"SELECT rating FROM performance_rating ORDER BY date_of_pr GROUP BY employee_ID
JOIN (SELECT employee_ID FROM employee WHERE manager_ID='".$m_id."') ON employee_ID";

and got "supplied argument is not a valid MySQL result resource"

[smerny]

actually, if i were to follow you more literally...

 

 

"SELECT rating FROM performance_rating ORDER BY date_of_pr GROUP BY employee_ID

JOIN performance_rating"?

 

not sure what you meant, but i have to use the employee table because i need to only include employees who are currently under a specific manager...

[premiso]

SELECT r.rating, DISTINCT r.employee_id FROM performance_rating r, employee e WHERE r.employee_ID = e.employee_ID AND e.manager_id = '" . $m_id . "' ORDER BY date_of_pr

 

Not sure if this will produce constant results, but should only pull out one rating per employee_ID.

[smerny]

SELECT r.rating, DISTINCT r.employee_id FROM performance_rating r, employee e WHERE r.employee_ID = e.employee_ID AND e.manager_id = '" . $m_id . "' ORDER BY date_of_pr

 

Not sure if this will produce constant results, but should only pull out one rating per employee_ID.

 

"supplied argument is not a valid MySQL result resource" for that as well.

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT r.employee_id FROM performance_rating r, employee e WHERE r.employee_' at line 1

[smerny]

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 manager

2) 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 had

3) ordering all those records by date descending would put those records in order starting with most recent date

4) 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)

 

[premiso]

Ah, no distinct won't work as it works on all columns returned. Just found that out. Maybe this will work...

 

SELECT rating, employee_id 
FROM performance_rating WHERE rating_id IN(SELECT DISTINCT pr1.rating_id,pr1.employee_id FROM employee e1, performance_rating pr1 WHERE e1.manager_id = '" . $m_id . "'  AND pr1.employee_id = e1.employee_ID ORDER BY date_of_pr)

 

I am unsure if that would work, seems like it might, but yea. Give it a try and see. (Sorry hard to test if I don't have the test data.)

 

And why that is not working for you, anything in the group by has to be returned in the select statement if I remember correctly, but GROUP BY is not what you want. That will still pull all ratings, given you are pulling that column.

[smerny]

after adjusting some table/attribute names to fit my db, i get this message

 

Operand should contain 1 column(s)

[premiso]

lol Oh yea. Sorry again:

 

SELECT pr.rating
FROM performance_rating pr, (
    SELECT DISTINCT pr1.rating_id, pr1.employee_id 
    FROM employee e1, performance_rating pr1 
    WHERE e1.manager_id = '" . $m_id . "'  AND pr1.employee_id = e1.employee_ID ORDER BY date_of_pr) pr2
WHERE pr2.rating_id = pr.rating_id ORDER BY pr.date_of_pr

 

Again not sure if it will work but maybe we are making headway!

[smerny]

your code with my tables names and "DESC" so the most recent date shows first:

$search = "SELECT pr.rating
FROM performance_review pr, (
    SELECT DISTINCT pr1.pr_ID, pr1.employee_ID 
    FROM employee e1, performance_review pr1 
    WHERE e1.manager_ID = '" . $m_id . "'  AND pr1.employee_ID = e1.employee_ID ORDER BY date_of_pr) pr2
WHERE pr2.pr_ID = pr.pr_ID ORDER BY pr.date_of_pr DESC";

 

this time there was no error, but it returned all the records rather than just the latest per employee

[premiso]

Well I am kind of baffled to the point of I do not know what to do.

 

An idea (which is probably a bad idea), is instead of this weird ass query, why not add a column "last_rating" to your employee table and just update that anytime a rating comes in. It would be a solution. The only other way would be to do two separate queries as far as I know, but yea. I am out of ideas for the queries lol. 

 

EDIT:

Or add a column to the preformance_rating  stating active, and always set the last one to the "Active" one (or name the column last_rating) so you can add that to the query.

[premiso]

Ok here is my latest attempt:

 

    SELECT p1.rating, MAX(p1.date_of_pr) as date_of_pr, e1.employee_id 
    FROM performance_review p1 JOIN employee e1 ON e1.employee_id = p1.employee_id 
    WHERE e.manager_id = $m_id ORDER BY p1.date_of_pr GROUP BY p1.employee_id, p1.rating;

 

This does have the possibilty of returning multiple rows per employee if two reviews were done on the same day, you will just have to code the PHP the only echo 1 per employee, at least this limits the number of rows returned etc.

[smerny]

$search = "SELECT p1.rating, MAX(p1.date_of_pr) as date_of_pr, e1.employee_ID 
    FROM performance_review p1 JOIN employee e1 ON e1.employee_ID = p1.employee_ID 
    WHERE e.manager_ID = '".$m_id."' ORDER BY p1.date_of_pr GROUP BY p1.employee_ID, p1.rating";

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY p1.employee_ID, p1.rating' at line 3

[awjudd]

I have an attempt as well ... this was my first trial ... I'm pretty sure that this could be simplified some more ... but here it is ...

 

SELECT e.employee_id, pr.rating
FROM Employee e
JOIN ( SELECT employee_id, MAX(date_of_pr) AS date_of_pr FROM performance_review pr WHERE e.employee_id = pr.employee_id GROUP BY pr.employee_id ) p ON e.employee_id = p.employee_id
JOIN ( SELECT employee_id, rating FROM performance_review pr WHERE p.employee_id = pr.employee_id AND p.date_of_pr = pr.date_of_pr ) pr ON p.employee_id = pr.employee_id AND p.date_of_pr
WHERE e.manager_id = $mid

 

The first join (p) will basically get a list for each employee under specific manager and the day of their last review.

 

The second join (pr) will then grab the corresponding reviews.

 

However, this will have the same problem as premiso's ...

 

Something doesn't look right ... and now it's bothering me ...

 

~juddster

[awjudd]

$search = "SELECT p1.rating, MAX(p1.date_of_pr) as date_of_pr, e1.employee_ID 
    FROM performance_review p1 JOIN employee e1 ON e1.employee_ID = p1.employee_ID 
    WHERE e.manager_ID = '".$m_id."' ORDER BY p1.date_of_pr GROUP BY p1.employee_ID, p1.rating";

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY p1.employee_ID, p1.rating' at line 3

 

ORDER BY comes after GROUP BY

 

$search = "SELECT p1.rating, MAX(p1.date_of_pr) as date_of_pr, e1.employee_ID 
    FROM performance_review p1 JOIN employee e1 ON e1.employee_ID = p1.employee_ID 
    WHERE e.manager_ID = '".$m_id."' GROUP BY p1.employee_ID, p1.rating ORDER BY p1.date_of_pr";

 

~juddster

[smerny]

I have an attempt as well ... this was my first trial ... I'm pretty sure that this could be simplified some more ... but here it is ...

 

SELECT e.employee_id, pr.rating
FROM Employee e
JOIN ( SELECT employee_id, MAX(date_of_pr) AS date_of_pr FROM performance_review pr WHERE e.employee_id = pr.employee_id GROUP BY pr.employee_id ) p ON e.employee_id = p.employee_id
JOIN ( SELECT employee_id, rating FROM performance_review pr WHERE p.employee_id = pr.employee_id AND p.date_of_pr = pr.date_of_pr ) pr ON p.employee_id = pr.employee_id AND p.date_of_pr
WHERE e.manager_id = $mid

 

The first join (p) will basically get a list for each employee under specific manager and the day of their last review.

 

The second join (pr) will then grab the corresponding reviews.

 

However, this will have the same problem as premiso's ...

 

Something doesn't look right ... and now it's bothering me ...

 

~juddster

 

"SELECT e.employee_ID, pr.rating
FROM employee e
JOIN ( SELECT employee_ID, MAX(date_of_pr) AS date_of_pr 
FROM performance_review pr 
WHERE e.employee_ID = pr.employee_ID 
GROUP BY pr.employee_ID) p 
ON e.employee_ID = p.employee_ID
JOIN ( SELECT employee_ID, rating 
FROM performance_review pr 
WHERE p.employee_ID = pr.employee_ID 
AND p.date_of_pr = pr.date_of_pr ) pr 
ON p.employee_ID = pr.employee_ID AND p.date_of_pr
WHERE e.manager_ID = '".$mid."'";

 

gives:

MySQL Error: Unknown column 'e.employee_ID' in 'where clause'

[smerny]

$search = "SELECT p1.rating, MAX(p1.date_of_pr) as date_of_pr, e1.employee_ID 
    FROM performance_review p1 JOIN employee e1 ON e1.employee_ID = p1.employee_ID 
    WHERE e.manager_ID = '".$m_id."' ORDER BY p1.date_of_pr GROUP BY p1.employee_ID, p1.rating";

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY p1.employee_ID, p1.rating' at line 3

 

ORDER BY comes after GROUP BY

 

$search = "SELECT p1.rating, MAX(p1.date_of_pr) as date_of_pr, e1.employee_ID 
    FROM performance_review p1 JOIN employee e1 ON e1.employee_ID = p1.employee_ID 
    WHERE e.manager_ID = '".$m_id."' GROUP BY p1.employee_ID, p1.rating ORDER BY p1.date_of_pr";

 

~juddster

"SELECT p1.rating, MAX(p1.date_of_pr) as date_of_pr, e1.employee_ID 
    FROM performance_review p1 JOIN employee e1 ON e1.employee_ID = p1.employee_ID 
    WHERE e.manager_ID = '".$m_id."' GROUP BY p1.employee_ID, p1.rating ORDER BY p1.date_of_pr";

gives:

 

Unknown column 'e.manager_ID' in 'where clause'

 

changed e to e1, it now gives me all the reviews (multiple per employee) in order by oldest first

[premiso]

e. should be e1. 

 

Simple typo.

[smerny]

"GROUP BY p1.employee_ID, p1.rating ORDER BY p1.date_of_pr"

 

to me... it the GROUP BY would only affect records of the SAME employee_ID with the SAME rating

 

then the order by would sort it...

 

so if employee_ID "10" had various records with different dates and such, but his ratings were 3,5,4,5,4,5 in that order according to dates...

 

after the GROUP BY and ORDER BY you'd have:

10 | 3

10 | 5

10 | 4

 

(it would remove multiple occurrence of the combination of same employee AND the same score, rather than removing all but the most recent record by each specific employee)

[premiso]

So it did not work?

 

If not, if you can provide the two table structures with (fake of course) test data for both tables, I can help you figure it out. Without it I am just pulling at straws.

[fenway]

I thought it was obvious.... you need to join on both the employee and the most recent date to make sure you're just getting a single record per employee.

[smerny]

but how do i get just the most recent date per employee?

 

SELECT rating FROM performance_review pr, employee e

WHERE e.manager_ID = '".$m_id."'

AND e.employee_ID = pr.employee_ID

AND pr.date_of_review = [most recent per employee]