CODEWITHSUNDEEP
mysql
Shreesha N
Shreesha N

Reputation: 922

Mysql Ranking within grouped results

I have read posts that answer how to rank results in mysql, but my question is how to assign ranks within a group

Let me explain with an example

Data:

  sem_id | result_month
  --------------------
   1    |1313907325000
   1    |1345529725000
   2    |1329804925000
   2    |1361427325000
   3    |1377065725000
   3    |1440137725000

What i am able to achieve with the below query:

SELECT @ss := @ss + 1 AS rank, 
       res.sm_id, 
       res.result_month 
FROM   (SELECT sm_id, result_month 
        FROM   xx_table
        GROUP  BY sm_id, 
                  result_month) AS res,(SELECT @ss := 0) AS ss;

Current results:

  rank | sem_id | result_month 
  ----------------------------
  1   | 1   |1313907325000
  2   | 1   |1345529725000
  3   | 2   |1329804925000
  4   | 2   |1361427325000
  5   | 3   |1377065725000
  6   | 3   |1440137725000

What I actually want :

  rank | sem_id | result_month 
  ----------------------------
  1   | 1   |1345529725000
  2   | 1   |1313907325000
  1   | 2   |1361427325000
  2   | 2   |1329804925000
  1   | 3   |1440137725000
  2   | 3   |1377065725000

In the above results things to observe is each group is ranked within itself and each group is ordered by result_month desc

Help me on how can i achieve the above results

Thanks in advance!

Upvotes: 2

Views: 1302

Answers (1)

Blank
Blank

Reputation: 12378

You're almost there, use another variable to compute group:

SELECT @ss := CASE WHEN @grp = sem_id THEN @ss + 1 ELSE 1 END AS rank, sem_id, result_month, @grp := sem_id
FROM   (select * from xx_table ORDER  BY sem_id, result_month DESC) m
CROSS JOIN (SELECT @ss := 0, @grp = null) ss

See demo here.

Upvotes: 1

Related Questions