SQL query to compile a list of game scores, eliminating progress scores, to sort by highest first

I have a table that looks roughly like this (see attached). The "score" is a cumulative score. So I only want the last score entry for the game (games are identified by the "code" column, all records with the same "code" are part of the same game). The score I am looking for will have the most recent timestamp, and it should also be the highest score for the game (since negative scores are never produced).

So I basically want a list of "top 10 high scores". I'm not sure how to do this since all the non-last scores are in the table. I'm guessing some kind of grouping is necessary.

Thank you in advance for any assistance!
timestamp,code,score,question,answer
7/16/2010 2:40:28 PM,466163992316,0,673,False
7/16/2010 2:41:02 PM,466163992316,0,673,False
7/16/2010 2:41:11 PM,466163992316,0,673,False
7/16/2010 2:43:11 PM,466163992316,0,673,False
7/16/2010 4:56:05 PM,466163992316,78,1212,True
7/16/2010 4:56:05 PM,466163992316,78,1212,True
7/16/2010 5:00:02 PM,466163992316,0,2064,False
7/16/2010 5:00:03 PM,466163992316,0,2064,False
7/16/2010 10:21:50 PM,466163992316,0,2166,False
7/17/2010 11:59:12 AM,466163992316,74,1695,True
7/18/2010 10:27:33 PM,246810,60,9,True
7/18/2010 10:27:40 PM,246810,134,2,True
7/18/2010 10:27:44 PM,246810,222,3,True
7/18/2010 10:27:48 PM,246810,310,1,True
7/18/2010 10:27:51 PM,246810,400,5,True
7/18/2010 10:28:00 PM,246810,464,6,True

Open in new window

Brad BansnerWeb DeveloperAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
select code, max(timestamp), max(score) group by code
order by max(score) desc
limit 10
0
 
vdr1620Commented:
Try using Rank Function as in below code

SELECT Top 10 * FROM
(
SELECT * , Rank () OVER (Partition By Code Order by Timestamp Desc) RankNo
)
WHERE RankNo = 1
ORDER BY ....
0
 
snoyCommented:
Hi,

what you mean by latest scores?
'coz if you wanna list the top 10 highest score there is no timestamp needed so a simple TSQL Select top 10.... will be enough

Can you give more explantions?

10x
H.Y.
www.w3consulting.ma
0
 
Brad BansnerWeb DeveloperAuthor Commented:
It looks like its that simple, thank you! I always get confused with grouping.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.