Calculating ranks from the following data

Question

How can I calculate the rank for all "type 10" rows in the data below using just sql?

The sql will go into a stored procedure, with no other scripting involved.

The parent holds the total of all rows in column total, and the total votes in votes.

I update the perCent col using this, so this should give you an idea. Maybe calculate ranks along with this?

All rows are linked by parent -> child relationship.

All is based on total votes and total candidates. Candidates are type 10

UPDATE likesd p
        JOIN likesd h
        ON p.parent = h.id
        AND p.country = h.country
    SET p.percent = TRUNCATE(100*p.votes/h.votes,2);

Raw Data

"id"    "type"  "parent"    "country"   "votes" "perCent"   "total" "rank"
"24"    "1"     "1"         "US"        "30"    "0"         ""      "0"
"25"    "3"     "24"        "US"        "30"    "0"         "3"     "0"
"26"    "10"    "25"        "US"        "15"    "50.00"     ""      "0"
"27"    "10"    "25"        "US"        "5"     "16.66"     ""      "0"
"28"    "10"    "25"        "US"        "10"    "33.33"     ""      "0"

Desired results

"id"    "type"  "parent"    "country"   "votes" "perCent"   "total" "rank"
"24"    "1"     "1"         "US"        "30"    "0"         ""      "0"
"25"    "3"     "24"        "US"        "30"    "0"         "3"     "0"
"26"    "10"    "25"        "US"        "15"    "50.00"     ""      "1" // Rank 1. Has 15 votes out of 30 (see parent row above)
"27"    "10"    "25"        "US"        "5"     "16.66"     ""      "3" // And so on.
"28"    "10"    "25"        "US"        "10"    "33.33"     ""      "2"

You should be able to use the answer in [this question](http://stackoverflow.com/questions/3333665/mysql-rank-function/3333697#3333697), except you use `IF(type = 10, @curRank := @curRank + 1 AS rank, 0) as Rank` — Barmar, Nov 15 '13 at 16:23
@barmar I'm close. If you read this could you help with this question: http://stackoverflow.com/questions/20015937/incorporating-a-ranking-system-into-this-sql I'm a little stuck with adding the `@` part in there. — jmenezes, Nov 16 '13 at 07:43

Mihai · Accepted Answer · 2013-11-15T16:39:03.327

1

SELECT id,type,parent,country,votes,perCent,total, FIND_IN_SET( votes, (
SELECT GROUP_CONCAT( votes
ORDER BY votes DESC ) 
FROM table WHERE type=10 )
) AS rank
FROM table

SQL Fiddle

UPDATE scores SET rank= (FIND_IN_SET(votes, (
SELECT * FROM(SELECT GROUP_CONCAT( votes
ORDER BY votes DESC ) 
FROM scores WHERE type=10)x ) ) )

edited Nov 15 '13 at 16:39

answered Nov 15 '13 at 16:24

Mihai

26,325
7
66
81

Works very nicely. I was hoping I'd be able to do it like in the update statement in my question, where all the children update their parents in one go, because this actually is an update. – jmenezes Nov 15 '13 at 16:34

Calculating ranks from the following data

1 Answers1