Rank of an row in MySql

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
User avatar
hakperest
Forum Newbie
Posts: 5
Joined: Sun Nov 04, 2007 6:18 pm

Rank of an row in MySql

Post 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.
User avatar
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

Post by Christopher »

You probably want to do something like:

Code: Select all

SELECT SUM(points) as total_points FROM mytable GROUP BY user;
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Rank of an row in MySql

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Rank of an row in MySql

Post 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.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Rank of an row in MySql

Post 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`
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Rank of an row in MySql

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Rank of an row in MySql

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Rank of an row in MySql

Post by pickle »

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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Rank of an row in MySql

Post by califdon »

pickle wrote:Ah, well then my query's useless.
Nah, not useless, just useful for a different purpose! :D
User avatar
hakperest
Forum Newbie
Posts: 5
Joined: Sun Nov 04, 2007 6:18 pm

Re: Rank of an row in MySql

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Rank of an row in MySql

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