Thursday, April 19, 2012

Counting the number of first place scores with mysql

Ok, so I have the following database:



CREATE TABLE IF NOT EXISTS `highscores` (
`lid` int(11) NOT NULL,
`username` varchar(15) NOT NULL,
`score` int(16) NOT NULL,
PRIMARY KEY (`lid`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


lid being the level id.



lets say I have the following values in the table:



lid, username,score

1,sam,15
1,joe,12
1,sue,6
1,josh,9
2,sam,8
2,joe,16
2,sue,4
3,sam,65
4,josh,87
4,sue,43
5,sam,12
5,sue,28
5,joe,29
and so on.


How would I create a query(or if required a set of queries) to get the following



sam has 3 high scores
joe has 2 high scores
josh has 1 high score


Thanks in advance.





No comments:

Post a Comment