CODEWITHSUNDEEP
phpmysql
Cully
Cully

Reputation: 470

Getting a rank from a SQL total column

I have a MySQL table like so...

ID | NAME  | RANK | PTS
---+-------+------+----
12 | John  | 1    | 28
18 | Andy  | 2    | 31
23 | Brian | 3    | 16
41 | Mike  | 4    | 33
15 | Jack  | 5    | 35
68 | Anne  | 6    | 24

I currently sort by...

SELECT * FROM `ranks` ORDER BY PTS desc

I am looking for some code that I can give it an ID, and it'll return it's ranking by the PTS column. For ID 41, it should return 2 as the 2nd best score of that column.

I am not sure what to google for to get an answer. Is there some SQL code that can do this simply? or maybe php? The RANK column is overall rank, and PTS is the current weekly score so far. There might be up to 2.5 million entries eventually. Please let me know if you have any questions.

Upvotes: 0

Views: 180

Answers (3)

joe
joe

Reputation: 556

try this query:

Step1:

CREATE TABLE t (
id INT(3),
name char(60),
score INT(5)
);

insert into t values(10,"name1",33);
insert into t values(11,"name2",43);
insert into t values(12,"name3",335);
insert into t values(13,"name4",233);

Step2:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, name, id, score from t order by score desc

Upvotes: 2

ping
ping

Reputation: 51

// get the number of ppl w/ points higher than the given user's
SELECT count(*) + 1 FROM ranks WHERE PTS > (
    SELECT PTS FROM ranks WHERE ID = 41);

...though if you are looking to scale this you probably want to reconsider how you are structuring your data.

Upvotes: 2

jfur7
jfur7

Reputation: 77

You just put the PTS into an array and create a loop to add the column output.

$sum = 0;
foreach ($array as $num){
    $sum = $sum + $num;
}
return $sum;

Upvotes: 0

Related Questions