Returning extra columns in a GROUP BY

Cowboy2w · June 25th, 2009, 12:22 PM

Greetings,
I have what appears to be a deceptively simple question. I have a table(Jobs) with a number of columns in it(10). I am only interested in 4 of those columns. They would be:

Project,
Employee_ID,
Job_Name,
Job_Start_Date

This table could contain, for these 4 columns:

'Beach', 'Emp01', 'Drive', 2009-04-01
'Beach', 'Emp01', 'Unpack', 2009-04-02
'Beach', 'Emp01', 'Blanket', 2009-04-03
'Beach', 'Emp02', 'Talk', 2009-05-02
'Beach', 'Emp03', 'Surf', 2009-06-14
'Beach', 'Emp03', 'BreakLeg', 2009-06-20

There could be more than one Project, but for this question and for sake of size, I have given only 1. What I would like to do is to take this result set and then return from it, based on the Project and Employee_ID, the highest Job_Start_Date and it's corresponding Job_Name.

I can write a select to get the MAX(Job_Start_Date), but then how to include the Job_Name?

/*
Non-Working example to show progression and stopping point due to cluelessness.
** No need to say this SELECT is incorrect or bad **
*/

SELECT
Project,
Employee_ID,
Job_Name, -- Needs to be the same Job_Name from the MAX(Job_Start_Date) record.
MAX(Job_Start_Date)
FROM Jobs
GROUP BY Project, Employee_ID

The final result should be:

'Beach', 'Emp01', 'Blanket', 2009-04-03
'Beach', 'Emp02', 'Talk', 2009-05-02
'Beach', 'Emp03', 'BreakLeg', 2009-06-20

I hope that this simplified example still provides enough information to help find a solution to my problem. If you need more information, please let me know and I will try to give you more.

Thank you for your time and help.

Terry Steadman

Cowboy2w · June 25th, 2009, 12:25 PM

One other piece of information. This is being done in SQL Server 2005.

Thank you for your time.

Terry Steadman

Old Pedant · June 26th, 2009, 01:09 AM

*ASSUMING* that you never have two identical Job_Start_Date values for the same Project, it's not hard. If you DO have more than one record with the same Job_Start_Date and same Project, then you'll need to add a differentiating field.

Anyway, with the assumption:

Code:

SELECT J.*
FROM Jobs, 
     ( SELECT project, MAX(job_start_date) AS maxdate
       FROM jobs GROUP BY project ) AS M
WHERE J.project = M.project
AND J.job_start_date = M.maxdate

Naturally, you don't have to SELECT J.*, you can get only whatever fields you want.