CODEWITHSUNDEEP
mysqldatabase
oxygen
oxygen

Reputation: 61

How to group data based on ranking in mysql

I am struggling to create query I want to group data by customer id based on score . customer have multiple score I want to combine customer score by their ranking below the table structure 

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` varchar(10) DEFAULT NULL,
  `score` int(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


insert  into `score`(`id`,`customer_id`,`score`) 
values (1,'C1',20),   (2,'C1',10),(3,'C3',30),(4,'C1',30),(5,'C2',40),
 (6,'C2',50),(7,'C2',20),(8,'C1',50),(9,'C3',20),
(10,'C1',50);

 Table result look like 
   id    customer_id    score
    1       C1                 20               
    2       C1                 10
    3       C3                 30
    4       C1                 30              
    5       C2                 40
    6       C2                 50
    7       C2                 20

Desire result :

       customer_id          score    Rank
         C1                 30        1       
         C1                 20        2
         C1                 10        3
         C2                 50        1       
         C2                 40        2
         C2                 20        3
         C3                 30        1

Upvotes: 1

Views: 67

Answers (2)

Pankaj katiyar
Pankaj katiyar

Reputation: 464

try this 

 SELECT
        a.score AS score,
        @rn := IF(@PREV = customer_id, @rn + 1, 1) AS rank,
        @PREV := customer_id AS cutomerId
    FROM score AS a
    JOIN (SELECT @PREV := NULL, @rn := 0) AS vars
    ORDER BY customer_id, score DESC, id

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

You can use variables for this:

SELECT id, customer_id, score,
       @rnk := IF(@cid = customer_id, @rnk + 1,
                  IF(@cid := customer_id, 1, 1)) AS rank
FROM score
CROSS JOIN (SELECT @rnk := 0, @cid := '') AS v
ORDER BY customer_id, score DESC

Demo here

Upvotes: 1

Related Questions