SQL statement select most recent date and group per id

Question

I am trying to retrieve the most recent 'topic 1 value' for each detailsID for the most recent date per detailsID.

I was thinking something along the lines of:

SELECT detailsID,`Topic 1 Scores`, MAX(Date) 
FROM Information.scores 
WHERE `Topic 1 Scores` IS NOT NULL 
GROUP BY `detailsID`,`Topic 1 Scores`

Is printing;

detailsID, Topic 1 Scores, MAX(Date)

2 0 26/09/2017

2 45 26/09/2017

2 100 26/09/2017

3 30 25/09/2017

3 80 14/10/2017

Rather than actually selecting the most recent date per detailsID?

How do you choose topic 70, both rows for detailid 1 have same date — M Khalid Junaid, Oct 14 '17 at 16:20
I can't see any difference between this question and the duplicate I selected. — Barmar, Oct 14 '17 at 16:20
Show your attempt at using one of the solutions there, and if it doesn't work we can reopen and show you how to do it correctly. — Barmar, Oct 14 '17 at 16:20
But that's not one of the solutions in the question I linked to. That will get the most recent date, it doesn't get the most recent score. — Barmar, Oct 14 '17 at 16:43

score 0 · Answer 1 · answered Oct 14 '17 at 23:41

A solution like this can be found in this answer in the linked question.

SELECT t1.detailsID, t1.`Topic 1 Scores`
FROM Information.scores AS t1
JOIN (SELECT detailsID, MAX(date) AS maxDate
      FROM Information.scores
      GROUP BY detailsID) AS t2 
ON t1.detailsID = t2.detailsID AND t1.date = t2.maxDate

SQL statement select most recent date and group per id

1 Answers1