3

How do I rank the total_score such that the highest total score has position 1, etc. If 2 or more have the same total score, then they should have the same position

CREATE TABLE `scores` (
  `id` int(11) NOT NULL auto_increment,
  `student_id` int(11) default NULL,
  `sessions_id` varchar(11) default NULL,
  `term_id` int(11) default NULL,
  `ass1` int(11) default NULL,
  `test1` int(11) default NULL,
  `proj1` int(11) default NULL,
  `ass2` int(11) default NULL,
  `test2` int(11) default NULL,
  `proj2` int(11) default NULL,
  `exam` int(11) default NULL,
  `class_id` int(11) default NULL,
  `subject_id` int(11) default NULL,
  `total_score` int(11) default NULL,
  `class_average` decimal(6,2) default NULL,
  `highest_score` int(11) default NULL,
  `lowest_score` int(11) default NULL,
  `position_in_subject` int(11) default NULL,
  `grade` varchar(2) default NULL,
  `teacher_remark` varchar(50) default NULL,
  `date_created` varchar(20) default NULL,
  `employee_id` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=latin1;

and here is my query

INSERT INTO `scores` VALUES ('1', '1', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '50', '1', '7', '80', '51.58', '80', '41', null, 'A2', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('2', '1', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '30', '1', '8', '60', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('3', '1', '2014/2015', '2', '6', '6', '6', '6', '6', '6', '6', '1', '9', '42', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('8', '5', '2014/2015', '2', '5', '6', '6', '6', '6', '6', '6', '1', '7', '41', '51.58', '80', '41', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('9', '5', '2014/2015', '2', '5', '4', '5', '4', '5', '4', '34', '1', '8', '61', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('10', '5', '2014/2015', '2', '5', '4', '4', '5', '5', '4', '5', '1', '9', '32', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('15', '8', '2014/2015', '2', '6', '6', '6', '6', '7', '7', '7', '1', '7', '45', '51.58', '80', '41', null, 'E', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('16', '8', '2014/2015', '2', '4', '3', '4', '3', '4', '3', '40', '1', '8', '61', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('17', '8', '2014/2015', '2', '4', '5', '4', '4', '5', '4', '23', '1', '9', '49', '35.00', '63', '22', null, '', null, '2015-04-05', '1');

-- suggestion include a student with the same total so that calculating the rank can be QA in a proper way

INSERT INTO `scores` VALUES ('4', '2', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '50', '1', '7', '80', '51.58', '80', '41', null, 'A2', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('5', '2', '2014/2015', '2', '5', '5', '5', '5', '5', '5', '30', '1', '8', '60', '56.33', '66', '29', null, 'C', null, '2015-04-05', '1');
INSERT INTO `scores` VALUES ('6', '2', '2014/2015', '2', '6', '6', '6', '6', '6', '6', '6', '1', '9', '42', '35.00', '63', '22', null, 'E', null, '2015-04-05', '1');
  • rpbouman.blogspot.com/2009/09/… – sahmada Apr 7 '15 at 11:44
  • 1
    I'm giving a +1 for having provided DML (CREATE TABLE...) and DDL (INSERT INTO...). If only everybody did the same! Just remember to format in future :-) – Vérace Apr 7 '15 at 12:20
4

I hate giving complete solutions but here you go:

START TRANSACTION;
create table total_sc_tmp as (select student_id, SUM(total_score) tot_sc
FROM
    scores
GROUP BY student_id);
select student_id,  tot_sc,
FIND_IN_SET(
tot_sc
, (SELECT 
    GROUP_CONCAT(
    distinct tot_sc
        ORDER BY tot_sc DESC) AS score
FROM
    total_sc_tmp)) as rank from total_sc_tmp;
  COMMIT;

reorder, use @row and avoid the tmp table if you want. Happy Easter to all of you with constructive comments

  • Guys, I really don't care about my ratings – ninjabber Apr 9 '15 at 16:40
  • The discussion isn't about ratings, it is about the appropriate use of flags. – Aaron Bertrand Apr 10 '15 at 17:12
3
SELECT  id,
        @seq := @seq + 1 AS seq,
        @rank := if(total_score = @sc, @rank, @seq) AS Rank,
        @sc := total_score AS Score
    FROM   ( SELECT  @sc := -1, @seq := 0, @rank := 0 ) init
    JOIN  scores
    ORDER BY  total_score DESC, id; 

To get rid of id and seq:

SELECT Rank, Score FROM ( <the SELECT above> ) ORDER BY Rank, id;

I'm unclear on whether total_score it the score for ranking or the SUM over student_id or the MAX for each "student". Depending on the intent, you may need to replace JOIN scores with JOIN ( <some subquery to get SUM or MAX> )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.