danielh
01-18-2008, 06:02 PM
Hi all,
Got a curly SQL problem. My database is MySQL 4.1.22. My table schema is like so:
id
game_id
user_id
score
player_name
submit_date
created_at
updated_at
deleted_at
My problem is: I want an ordered list of high scores with only one entry/score per user with scores ordered by score descending, submit_date ascending. I need to retrieve all the fields of the score.
This would be enough for me as i can do the rest 'manually', but if anyone knows i also need the top 10 ranked of those scores. this means if 10th, 11th and 12th best scores are all the same then i need to retrieve 12 scores (similiar to olympics if there are equal 3rd place then 4 medals are given)
Currently i have this:
SELECT high_score.*, max( score ) AS best
FROM high_score
WHERE game_id = 1
GROUP BY user_id
ORDER BY best DESC, submit_date ASC
It nearly works; it retrieves me the best score of each user associated with their user id, but the rest of the data is from any old high score for that user. e.g. if user_id 1 scores 450, 500 and 650 the result might return the high_score info for the 450 high_score but with the added best field
Does anyone know the answer?
P.S. I'm using Propel via Symfony so if by any chance you are familiar with Propel and it is easier to do it through Criteria/ can do it through Criteria then that would be much appreciated also :)
Got a curly SQL problem. My database is MySQL 4.1.22. My table schema is like so:
id
game_id
user_id
score
player_name
submit_date
created_at
updated_at
deleted_at
My problem is: I want an ordered list of high scores with only one entry/score per user with scores ordered by score descending, submit_date ascending. I need to retrieve all the fields of the score.
This would be enough for me as i can do the rest 'manually', but if anyone knows i also need the top 10 ranked of those scores. this means if 10th, 11th and 12th best scores are all the same then i need to retrieve 12 scores (similiar to olympics if there are equal 3rd place then 4 medals are given)
Currently i have this:
SELECT high_score.*, max( score ) AS best
FROM high_score
WHERE game_id = 1
GROUP BY user_id
ORDER BY best DESC, submit_date ASC
It nearly works; it retrieves me the best score of each user associated with their user id, but the rest of the data is from any old high score for that user. e.g. if user_id 1 scores 450, 500 and 650 the result might return the high_score info for the 450 high_score but with the added best field
Does anyone know the answer?
P.S. I'm using Propel via Symfony so if by any chance you are familiar with Propel and it is easier to do it through Criteria/ can do it through Criteria then that would be much appreciated also :)