How to return max date based on two other columns?

Question

I haven't been able to track down an answer to this problem, so I am hoping someone here can help. I need to return the max date for each course, for each ID. I have been using a subquery to pick the max date based from one other column, but cannot seem to find a way to have it take into account both columns. Data would look something like this:

ID    Course    CourseDate
1     DD        1/14/2013
1     DD        1/16/2013
1     CC        2/22/2013
1     CC        2/15/2013
2     DD        1/16/2013

I am hoping to get a result that would look like this:

ID    Course    CourseDate
1     DD        1/16/2013
1     CC        2/22/2013
2     DD        1/16/2013

Thank you very much for any help! It is greatly appreciated!

score 1 · Accepted Answer · answered Mar 05 '13 at 15:52

1

use GROUP BY and MAX()

SELECT  ID, Course, MAX(CourseDATE) MAx_DATE
FROM    TableName
GROUP   BY ID, Course

answered Mar 05 '13 at 15:52

John Woo

258,903
69
498
492

It was just that easy apparently. I was trying to make it harder than it needed to be. Thank you for the response! – user1563370 Mar 05 '13 at 16:46

How to return max date based on two other columns?

1 Answers1