SQL retrieve recent record

Question

I want to retrieve TOPIC 1 SCORES with the most recent score (excluding null) (sorted by date) for each detailsID, (there are only detailsID 2 and 3 here, therefore only two results should return)

It does not return ***per `detailsID`*** because this is not what you put in your `GROUP BY`. — PM 77-1, Oct 14 '17 at 21:23
Possible duplicate of [How can I select rows with most recent timestamp for each key value?](https://stackoverflow.com/questions/17327043/how-can-i-select-rows-with-most-recent-timestamp-for-each-key-value) — dognose, Oct 14 '17 at 23:14

score 0 · Answer 1 · answered Oct 14 '17 at 21:28

0

What about getting rid of Topic 1 Scores in GROUP BYdetailsID,Topic 1 Scores ?

answered Oct 14 '17 at 21:28

David Floydd

1
2

score 0 · Answer 2 · edited Oct 15 '17 at 07:13

0

Assuming SQL Server:

SELECT  
    ROW_NUMBER() OVER (PARTITION BY detailsID ORDER BY Date DESC) AS RowNumber,
    detailsID, Date, Topic 1 Scores
FROM 
    Information.scores

edited Oct 15 '17 at 07:13

marc_s

732,580
175
1,330
1,459

answered Oct 14 '17 at 21:38

Erik

384
1
9

score 0 · Answer 3 · answered Oct 14 '17 at 21:46

0

Use a subquery to get the max and then join to it.

SELECT a.detailsID,`Topic 1 Scores`, a.Date 
FROM Information.scores AS a
JOIN (SELECT detailsID, MAX(Date) "MaxDate"
      FROM Information.scores
      WHERE `Topic 1 Scores` IS NOT NULL
      GROUP BY detailsID) Maxes
ON a.detailsID = Maxes.detailsID
   AND a.Date = Maxes.MaxDate
WHERE `Topic 1 Scores` IS NOT NULL

answered Oct 14 '17 at 21:46

JBrooks

9,901
2
28
32

'2','100','26/09/2017' '2','0','26/09/2017' '2','45','26/09/2017' '3','30','25/09/2017' This is returned instead, – Lottie T Oct 14 '17 at 22:34
So you have ties in the Date per detailsID? Do you have any other IDs or a DateTime? – JBrooks Oct 14 '17 at 22:44
@LottieT Please post a sample of your data with all of the columns you have. – JBrooks Oct 15 '17 at 00:23

score -1 · Answer 4 · answered Oct 14 '17 at 21:19

-1

Try doing

SELECT detailsID,`Topic 1 Scores`, MAX(Date) as "Date" GROUP BY "Date"

answered Oct 14 '17 at 21:19

Alex Dovzhanyn

1,028
6
14

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY "Date"' at line 1 – Lottie T Oct 14 '17 at 21:20
You need to add which table its selecting from (FROM tablename) – Alex Dovzhanyn Oct 14 '17 at 21:22

SQL retrieve recent record

4 Answers4