CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

Incorporating an incrementing value into this sql

I'm trying to do something like the first answer in this question Adding the @part which outputs a rank, somehow I'm not able to get it right.

The sql I'm using is:

select child.id, child.perCent
from likesd parent
join likesd child
   on parent.id = child.parent
where parent.type = 3
order by parent.id, child.perCent desc;

I some how cannot fit the @ parts inside the above sql, and need help there.

SELECT    first_name, // This sql is from the previous question
          age,
          gender,
          @curRank := @curRank + 1 AS rank
FROM      person p, (SELECT @curRank := 0) r
ORDER BY  age;

So:

select child.id, child.perCent, @curRank := @curRank + AS rank
    from likesd parent, (SELECT @curRank := 0) r
    join likesd child
       on parent.id = child.parent
    where parent.type = 3
    order by parent.id, child.perCent desc;

In the end, what I'm trying to achieve is in the Desired results. Can you see how I can do this?

Main Table

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

"29"    "1"     "1"         "US"        "50"    "0"
"30"    "3"     "29"        "US"        "50"    "0"
"31"    "10"    "30"        "US"        "20"    "40.00"
"32"    "10"    "30"        "US"        "15"    "25.00"
"33"    "10"    "30"        "US"        "15"    "35.00"

Expected results:

"id"    "perCent" "rank" // Rank is calculated based on the sql order above
"26"    "50.00"   "1" 
"27"    "33.33"   "2"
"28"    "16.66"   "3"

"31"    "40.00"    "1" // New parent, new ranking
"33"    "35.00"    "2"
"32"    "25.00"    "3"

Upvotes: 1

Views: 76

Answers (2)

Barmar
Barmar

Reputation: 780994

select id, perCent, 
       @curRank := if(parent = @prevParent, @curRank + 1, 1) AS rank,
       @prevParent := parent
from (
    select child.id, child.perCent, child.parent
    from likesd parent
    join likesd child
       on parent.id = child.parent
    where parent.type = 3
    order by parent.id, child.perCent desc) x
cross join (SELECT @curRank := 0, @prevParent := null) r

FIDDLE

Upvotes: 1

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

Try this :

select child.id, 
       child.perCent, 
       CASE parent.id 
      WHEN @curParent THEN @curRank := @curRank + 1 
      ELSE @curRank := 1 AND @curParent := parent.id  END as Rank
from  likesd parent, likesd child, (SELECT  @curParent := 0, @curRank := 0) r
where parent.id = child.parent
and   parent.type = 3
order by parent.id, child.perCent desc;

Upvotes: 1

Related Questions