SQL use MAX() function select field not in Group By

Question

I want to find the most recent entry for a unique combination of two fields in my table.

Employee_Number | Cap_Id | Score | Date_Added

I want to find the most recent 'Date_Added' for a group of Employee_Number and Cap_Id but also show the 'Score' entered for that group. I can Group By Employee_Number and Cap_Id to get the row I want in the result, but I can't also show the Score as Group By will determine it as a distinct value.

If my table looks like:

Employee_Number Cap_Id  Score   Date_Added
96149             2       4     04/06/2015
96149             2       3     03/06/2015

I want the result to be:

Employee_Number Cap_Id  Score   Date_Added
96149             2       4     04/06/2015

I can use:

SELECT Employee_Number, Cap_Id, MAX(Date_Added)
FROM Scores
GROUP BY Employee_Number, Cap_Id

To get the correct result, but I need the Score too

Answer 1

Skip the GROUP BY, return a row if no other row with same Employee_Number and Cap_Id but a later date exists!

SELECT Employee_Number, Cap_Id, Score, Date_Added
FROM Scores s1
WHERE NOT EXISTS (select 1 from Scores s2
                  where s1.Employee_Number = s2.Employee_Number
                    and s1.Cap_Id = s2.Cap_Id
                    and s1.Date_Added < s2.Date_Added)

It'll return both rows if there's a tie!

Edit - some kind of explanation:

The sub-query looks for another row with same Employee_Number and Cap_Id, but a later Date_Added value.

If no such row exists, the row from the main select is returned.

What to select in the sub-select is of no importance, the important thing is if a row exists or not exists. (That's the select 1. You can select whatever in the sub-query, if doesn't matter.)