If Duplicate and If another Duplicate then Pull the most recent data

[Lorlai]

I have a set of client data that is not in a very good format, but have been tasked with trying to run several reports on the data by the end of today. I need to create a query that will sort the data by the employee number, check to see if there are duplicate course titles, and if there are duplicates, discard all but the most recent dates.

EMP_ID	COURSE_NAME	DATE_COMPLETED
123	Getting an A	1/25/2013
123	Getting an A	7/08/2009
456	Don't Fail	3/26/2003
456	Make New Friends	1/16/2013
456	Make New Friends	4/25/2009

I want to create a query that will leave me data looking like this:

EMP_ID	COURSE_NAME	DATE_COMPLETED
123	Getting an A	1/25/2013
456	Don't Fail	3/26/2003
456	Make New Friends	1/16/2013

I'm not sure how to even begin with this. Currently, we are pulling data from one data set, matching on the employee number, and grouping by the employee number:

SELECT [Report Data].EMPLOYEE_ID, [Report Data].COURSE_NAME, [Report Data].DATE_COMPLETED
FROM [EE Numbers Report] INNER JOIN [Report Data] ON [EE Numbers Report].[EE Number] = [Report Data].EMPLOYEE_ID
GROUP BY [Report Data].EMPLOYEE_ID, [Report Data].COURSE_NAME, [Report Data].DATE_COMPLETED;

Any help with this would be greatly appreciated!

[jzwp11]

You could try using the Max function on the date field to get the most recent. You will need to remove the date field from the GROUP BY clause

SELECT [Report Data].EMPLOYEE_ID, [Report Data].COURSE_NAME, MAX([Report Data].DATE_COMPLETED) As MostRecent
FROM [EE Numbers Report] INNER JOIN [Report Data] ON [EE Numbers Report].[EE Number] = [Report Data].EMPLOYEE_ID
GROUP BY [Report Data].EMPLOYEE_ID, [Report Data].COURSE_NAME

[Lorlai]

Thank you for your help! This worked perfectly (and sorry for the long delay in marking this as complete!).

[jzwp11]

You're welcome.