how to sort dated scored data

So I have a table that contains 3 relevant fields, "User", "Score", and "Date". What I want to do is return sorted data, so that it is sorted by Score, but only paying attention to the entry for each user which has the most recent date.

Hopefully that made sense.

Thank you very much for any help.

 

This may take some tweaking but this should work:

SELECT user, score, date FROM user_table GROUP BY user ORDER BY date DESC, score DESC;

 

thank you, I'll try that out!

 

hmm, I got it to ALMOST work with this:

SELECT user, max(Date), Score FROM table GROUP BY user ORDER BY Score DESC

it's pulling the correct user and the correct latest date, just the score doesn't match up with what it should be.

 

Which is more important, the max date or the max score.

You can do:
SELECT user, max(Date), Score FROM table GROUP BY user ORDER BY Score DESC

or:
SELECT user, Date, max(Score) FROM table GROUP BY user ORDER BY Date DESC

depending on what you want the output to be.

 

SELECT user, max(Date), Score FROM table GROUP BY user ORDER BY Score DESC

seems like it would be working fine, except its returning rows that don't even exist.

For example, there is data
user, date, score
------------------------
496, 2008-10-10, 4
496, 2008-11-10, 9999

and it is pulling a row:
user, date, score
------------------------
496, 2008-11-10, 4


On closer inspection of exactly what mysql can do, I think it might be impossible for me to do exactly what I want it to do, so maybe I should just make a new table that only has the most recent score for each user, specifically for the purpose of creating top 10 tables (the reason why I am trying to do this)

 

Did the original:
ORDER BY date DESC, score DESC;

or:
ORDER BY score DESC, date DESC;

not work. This would keep the records being pulled correct.

 

Sorting on multiple columns would work.

 

SELECT user,score,date FROM myscoretable WHERE CONCAT(user,date) IN (SELECT CONCAT (user,MAX(date)) FROM myscoretable GROUP BY user) ORDER by score

I don't have an SQL server at the moment but this should do what you want.

 

Give this a go.

select
person,
max(date) as Date,
(select top 1 score from users u2 where u2.person = u.person and u2.date = max(u.date) order by score desc) as Score
from
users U
group by person
order by score DESC

I imagine there is an easier way to do it, but that works on MS SQL Server and should be generic to work on pretty much anything.

If you are using MS SQL Server 2005 or above, this would also work

WITH Users_CTE
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Person ORDER BY Date DESC) AS ROW,
Person,
Date,
Score
FROM
Users
)
Select person, Date, Score from Users_CTE where row = 1 order by score desc

Which according to Query Analyser is more efficient although I've not put any optimisations into the table which may change things around.

Jen