CODEWITHSUNDEEP
phpmysql
Norris
Norris

Reputation: 91

Mysql rank with condition

I have a race result table like below : 

table result_race_to_registration 

 id  | event_id |  result_race_id | registration_id  |  subclass_id  | 
---------------------------------------------------------------------
1   |     1    |        21       |        11          |       1     |
---------------------------------------------------------------------
2   |     1    |        22       |        12          |       1     |
---------------------------------------------------------------------
3   |     1    |        23       |        13          |       1     |
---------------------------------------------------------------------
4   |     1    |        24       |        14          |       2     |
---------------------------------------------------------------------
5   |     1    |        25       |        15          |       2     |
---------------------------------------------------------------------
6   |     1    |        26       |        16          |       2     |
---------------------------------------------------------------------
7   |     2    |        27       |        17          |       1     |
---------------------------------------------------------------------
8   |     2    |        28       |        18          |       1     |
---------------------------------------------------------------------
9   |     2    |        29       |        19          |       1     |
---------------------------------------------------------------------
10  |    2     |        30       |        20          |       2     |
---------------------------------------------------------------------
11  |     2    |        31       |        21          |       2     |
---------------------------------------------------------------------
12  |     2    |        32       |        22          |       2     |
---------------------------------------------------------------------

Expected result 
rank  | event_id |  result_race_id | registration_id    | subclass_id | 
------------------------------------------------------------------------
   1   |    1     |        21       |        11          |       1     |
------------------------------------------------------------------------
   2   |    1     |        22       |        12          |       1     |
------------------------------------------------------------------------
   3   |    1     |        23       |        13          |       1     |
------------------------------------------------------------------------
   1   |    1     |        24       |        14          |       2     |
------------------------------------------------------------------------
   2   |    1     |        25       |        15          |       2     |
------------------------------------------------------------------------
   3   |    1     |        26       |        16          |       2     |
------------------------------------------------------------------------

How can i get the rank by for every subclass_id filter by event_id. I've already try a query like this 

SELECT @rank:=CASE WHEN @subclass = rtr.subclass_id  THEN @rank+1 ELSE 1 END AS rank, @subclass:= rtr.subclass_id AS subclass_id_1 , rtr.result_race_id, FROM result_race_to_registration rtr WHERE rtr.event_id = '$event_id' ORDER BY rtr.subclass_id,rank ASC

But whenever i put event_id on WHERE condition the rank is not valid by each subclass. Please give some clue. Thanks

Upvotes: 0

Views: 356

Answers (1)

Trung Dao
Trung Dao

Reputation: 50

Try using this, 2 sub-queries should help

SET @event_ID=0, @subclass_ID=0, @Rank = 0;
SELECT 
    b.Rank,
    b.event_id, 
    b.result_race_id, 
    b.registration_id, 
    b.subclass_id
FROM (
        SELECT 
            if(@subclass_ID=A.subclass_id and @event_ID=a.event_ID,@Rank:=@rank+1,@Rank:=1) as Rank,
            @subclass_ID:=a.subclass_id,
            @event_ID:=a.event_ID,
            a.*
        FROM 
            ( 
                SELECT *
                FROM result_race_to_registration 
                ORDER BY event_id, subclass_id asc
            ) a
        ) b

Upvotes: 1

Related Questions