Rank of an row in MySql
Moderator: General Moderators
Rank of an row in MySql
Dear Experts
I want obtain the rank of any rows at any selected mysql command. for example I want sea the rank of a user points from total points.
example:
row point
1 20
2 19
...
20 5
21 4.5
22 4
I want a query that show 21 for a user that took 4.5 point. this example may be have 100 000 records.
Please introduce me a query that can do this.
I want obtain the rank of any rows at any selected mysql command. for example I want sea the rank of a user points from total points.
example:
row point
1 20
2 19
...
20 5
21 4.5
22 4
I want a query that show 21 for a user that took 4.5 point. this example may be have 100 000 records.
Please introduce me a query that can do this.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Rank of an row in MySql
You probably want to do something like:
Code: Select all
SELECT SUM(points) as total_points FROM mytable GROUP BY user;(#10850)
Re: Rank of an row in MySql
How about something like this:
You need to avoid relying on "rows", since relational database theory does not guarantee that rows in a table will be in any particular order.
Code: Select all
SELECT COUNT([id]) AS rank
FROM mytable
WHERE [points] >= (SELECT [points] FROM mytable WHERE [user] = $user)- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: Rank of an row in MySql
MySQL FULLTEXT searching ranks results based on some algorithm that I am not sure of, and you can select the ranking, however it is in terms of 0-1 and FULLTEXT has some limitations in MySQL.
Re: Rank of an row in MySql
califdon is closest to the answer, but still not correct, it won't work correctly if there are many users with the same amount of points.
You need two steps (I'll write them in two separate queries for clarity, you should combine them). You must also define a secondary (and more if needed) key for sorting users.
1.
2.
You need two steps (I'll write them in two separate queries for clarity, you should combine them). You must also define a secondary (and more if needed) key for sorting users.
1.
Code: Select all
SELECT COUNT(*) as `greater` FROM `mytable` WHERE `points`>'$nMyPoints'Code: Select all
SELECT COUNT (*) as `equal` FROM `mytable` WHERE `points`='$nMyPoints' AND `secondary_sort_key` < `$sMySecondarySortKey`Re: Rank of an row in MySql
I'm not 100% sure what you're asking. Do you want to be able to order your results & then pick the 21st row (or 5th, or 1000th, or some other number)?
If I understand you correctly, then this query should work:
[sql]SELECT *FROM `total_points`ORDER BY `points`LIMIT 21,1[/sql]
Replace "21" with whichever rank you want.
If I understand you correctly, then this query should work:
[sql]SELECT *FROM `total_points`ORDER BY `points`LIMIT 21,1[/sql]
Replace "21" with whichever rank you want.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: Rank of an row in MySql
I believe he wants to know, for a particular ID, what is its rank among the other IDs, according to the scores for each.pickle wrote:I'm not 100% sure what you're asking. Do you want to be able to order your results & then pick the 21st row (or 5th, or 1000th, or some other number)?
Re: Rank of an row in MySql
Ah, well then my query's useless.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: Rank of an row in MySql
Nah, not useless, just useful for a different purpose!pickle wrote:Ah, well then my query's useless.
Re: Rank of an row in MySql
Dear experts.
I want know what is its rank among the other IDs. for example I need to know US and Uk rank from country tables that each country has specified value.
example
conutry GDP
FR 850
Gr 950
Us 1200
CA 1000
Uk 980
Tr 900
...........
I want take Us => 1 , Uk => 3
select *,rank from table order by GDP desc
I want know what is its rank among the other IDs. for example I need to know US and Uk rank from country tables that each country has specified value.
example
conutry GDP
FR 850
Gr 950
Us 1200
CA 1000
Uk 980
Tr 900
...........
I want take Us => 1 , Uk => 3
select *,rank from table order by GDP desc
Re: Rank of an row in MySql
If your data does not contain entries with duplicate scores (a tip of the hat to Mordred for noting that exception), my previous suggestion should work. If the possibility exists that more than one entry might have the same 'points', then you would need to pursue Mordred's suggestion or something similar.