CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

MySql increment or not based on a previous value

In the below sql, I'm trying to increment the rank, based on the perCent column. The thing here is, that the rank should remain the same when a similar score is found.

select id, perCent, 
       @curRank := if( parent = @prevParent and perCent != @prevPerCent , @curRank + 1, 1) AS rank,

       @prevParent := parent, @prevPerCent := perCent
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, @prevPerCent := null) r

Eg: 50 is rank 1, 30 is rank 2, 30 is again rank 2, 20 is rank 3. The idea is to not increment when a similar rank is found.

How do I do that? How can I tell MySql in the if statement not to incremwnt it?

The basic logic is

if parent = @prevParent, if perCent = @prevPerCent
// Dont increment
else
increment

I'm stuck with this part in MySql. Can you help?

Main Table

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

"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"
"26"    "50.00"   "1"
"27"    "33.33"   "2" 
"28"    "33.33"   "2" // No increment in rank if the perCent is same as above

"31"    "40.00"    "1" // Continious incrementing here. PerCents differ.
"33"    "35.00"    "2"
"32"    "25.00"    "3"

Some more effort that dont work (Ugh! I give up)

select id, perCent, 
      @curRank := if(parent = @prevParent, TRUE, FALSE) AS rank,

      @curCent := if(perCent = @prevCent, FALSE, TRUE) AS cent,

      @curRank := if(@curRank and @curCent, @curRank + 1,'No Inc') AS k,

       @prevParent := parent, @prevCent := perCent
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, @prevCent := null) r

Upvotes: 1

Views: 147

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24470

Try this: http://sqlfiddle.com/#!2/ac996/7

select id, perCent, rank
from
(
    select id, perCent 
    , @curRank :=   
        case 
           when @prevParent = parent then
               case
                   --if parents are the same and values are the same, rank is the same
                   when @prevCent = perCent then @curRank 
                   --if same parents but different values, increment the rank
                   else @curRank + 1
               end
           --if parents are different, reset the rank
           else 1
        end rank
    , @prevParent := parent
    , @prevCent := perCent
    from 
    (
        select child.id, child.perCent, child.parent
        from likesd parent
        inner join likesd child
           on child.parent = parent.id
        where parent.type = 3
        order by parent.id
        , child.perCent desc
    ) x
    cross join (SELECT @curRank := 0, @prevParent := null, @prevCent := null) r
) y

Upvotes: 1

Related Questions