Page 1 of 1

Ranking query

Posted: Tue Jul 25, 2006 12:40 pm
by someberry
I wanted to make a small ranking query which gave people their rank based on the score they have throughout the site. The query that I have so far is:

Code: Select all

SELECT count(id) AS rank
FROM `country`
WHERE `score` >= '" . $user['s'] . "'
ORDER BY `score` DESC, `id` ASC
Basically it counts how many people have a higher score than them and then uses that as a rank. It works great until you have people with an equal score, it will then do this:

Rank 1 : 100 points
Rank 2 : 98 points
Rank 2 : 98 points
Rank 4 : 95 points

I would like it so it goes Rank 1, 2, 3, 4. It doesnt work because the order by is just ordering the ranks which the count isnt counting. I hope you understand what I mean and that it is possible with a single query.

Thanks,
someberry.

Posted: Tue Jul 25, 2006 12:42 pm
by JayBird

Code: Select all

GROUP BY rank
????

Posted: Tue Jul 25, 2006 12:50 pm
by someberry

Code: Select all

SELECT count(id) AS rank
FROM `country`
WHERE `score` >= '" . $user['s'] . "'
GROUP BY rank
ORDER BY `score` DESC, `id` ASC

Code: Select all

Can't group on 'rank'
:(

Posted: Tue Jul 25, 2006 12:51 pm
by feyd
GROUP BY COUNT(id)

Posted: Tue Jul 25, 2006 12:55 pm
by someberry

Code: Select all

SELECT COUNT(id) AS rank
FROM `country`
WHERE `score` >= '" . $user['s'] . "'
GROUP BY COUNT(id)
ORDER BY `score` DESC, `id` ASC

Code: Select all

Invalid use of group function
:(

Posted: Tue Jul 25, 2006 1:02 pm
by feyd
oops, brainfart.. GROUP BY id

Posted: Tue Jul 25, 2006 1:13 pm
by someberry
Hmm, this time no errors, but people still share the same rank :(

Code: Select all

SELECT COUNT(id) AS rank
FROM `country`
WHERE `score` >= '" . $user['s'] . "'
GROUP BY `id`
ORDER BY `score` DESC, `id` ASC