CODEWITHSUNDEEP
mysqlsql
user1343099
user1343099

Reputation: 49

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.

Upvotes: 2

Views: 165

Answers (2)

liquorvicar
liquorvicar

Reputation: 6106

From what you've described this query will produce what you need

SELECT username,COUNT(*) as num_highscores FROM (
SELECT lid,username
FROM highscores h1
WHERE score=(
    SELECT MAX(score) 
    FROM highscores h2 
    WHERE h2.lid=h1.lid
)
) AS high_scores
GROUP BY username
ORDER BY num_highscores DESC

Although the results I get on your sample data are different:

  • sam 2
  • joe 2
  • josh 1

Upvotes: 2

Vikram
Vikram

Reputation: 8333

i have not tested it, but try the following query

select 
    concat(h.username ," has ", count(h.username)," high scores ")
from
    highscores h inner join
    (select lid, max(score) as maxscore
     from highscores group by lid) t on h.lid = t.lid and h.score = t.maxscore
group by h.username

Upvotes: 2

Related Questions