Ranking query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Ranking query

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Code: Select all

GROUP BY rank
????
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post 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'
:(
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

GROUP BY COUNT(id)
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post 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
:(
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

oops, brainfart.. GROUP BY id
someberry
Forum Contributor
Posts: 172
Joined: Mon Apr 11, 2005 5:16 am

Post 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
Post Reply