Incorporating an incrementing value into this sql

Question

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"

Why do you expect 31, 32, and 33 in the result? They have parent = 29, whose type is 1, not 3. — Barmar, Nov 16 '13 at 08:39
@Barmar Sorry, my bad. I made edits to the data. Posting all of it wold make things difficult for others. — jmenezes, Nov 16 '13 at 08:46

Upendra Chaudhari · Answer 1 · 2013-11-16T08:33:04.927

1

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;

edited Nov 16 '13 at 08:33

answered Nov 16 '13 at 08:07

Upendra Chaudhari

6,473
5
25
42

There's an error at AS rank in your sql, that I cant get across since the past 10 minutes. Any idea? – jmenezes Nov 16 '13 at 08:20
Works just like the way I did. But that's not the idea. See the desired results. The rank needs to reset for each parent. That's where I got stuck too. – jmenezes Nov 16 '13 at 08:24
Works well. Just one small question, you've deviated from my original sql. Your sql and mine will output the same result, right? There wont be problems... – jmenezes Nov 16 '13 at 08:42
Yes, I have used your query to do it, just changed join condition – Upendra Chaudhari Nov 16 '13 at 09:35

Barmar · Accepted Answer · 2013-11-16T08:50:50.617

1

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

edited Nov 16 '13 at 08:50

answered Nov 16 '13 at 08:42

Barmar

741,623
53
500
612

:-) This gives a continuous ranking. Just like what I achieved. The idea is to start with 1 for each parent children (like in desired results). I've corrected the question where 31,32,33 had wrong parents. – jmenezes Nov 16 '13 at 08:49
Updated the answer to do per-parent ranking. – Barmar Nov 16 '13 at 08:52
Thanks, Barry. I can see this wouldn't have been possible with basic sql. – jmenezes Nov 16 '13 at 08:54

Incorporating an incrementing value into this sql

2 Answers2

Linked