Page 1 of 1
Rank of an row in MySql
Posted: Sat Jan 12, 2008 9:14 am
by hakperest
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.
Re: Rank of an row in MySql
Posted: Sat Jan 12, 2008 1:16 pm
by Christopher
You probably want to do something like:
Code: Select all
SELECT SUM(points) as total_points FROM mytable GROUP BY user;
Re: Rank of an row in MySql
Posted: Sat Jan 12, 2008 1:50 pm
by califdon
How about something like this:
Code: Select all
SELECT COUNT([id]) AS rank
FROM mytable
WHERE [points] >= (SELECT [points] FROM mytable WHERE [user] = $user)
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.
Re: Rank of an row in MySql
Posted: Sat Jan 12, 2008 5:09 pm
by RobertGonzalez
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
Posted: Sun Jan 13, 2008 3:13 am
by Mordred
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.
Code: Select all
SELECT COUNT(*) as `greater` FROM `mytable` WHERE `points`>'$nMyPoints'
2.
Code: Select all
SELECT COUNT (*) as `equal` FROM `mytable` WHERE `points`='$nMyPoints' AND `secondary_sort_key` < `$sMySecondarySortKey`
Re: Rank of an row in MySql
Posted: Mon Jan 14, 2008 11:03 am
by pickle
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.
Re: Rank of an row in MySql
Posted: Mon Jan 14, 2008 7:03 pm
by califdon
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)?
I believe he wants to know, for a particular ID, what is its rank among the other IDs, according to the scores for each.
Re: Rank of an row in MySql
Posted: Tue Jan 15, 2008 9:48 am
by pickle
Ah, well then my query's useless.
Re: Rank of an row in MySql
Posted: Tue Jan 15, 2008 8:24 pm
by califdon
pickle wrote:Ah, well then my query's useless.
Nah, not useless, just useful for a different purpose!

Re: Rank of an row in MySql
Posted: Sun Jan 20, 2008 4:31 am
by hakperest
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
Re: Rank of an row in MySql
Posted: Sun Jan 20, 2008 11:44 am
by califdon
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.