CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10), score int);

INSERT INTO student VALUES 

  ('Maths', 60, 'Thulile', 1515),

  ('Maths', 60, 'Pritha', 2000),

  ('Maths', 70, 'Voitto', 2000),

  ('Maths', 55, 'Chun', 1600),

  ('Biology', 60, 'Bilal', 3000),

  ('Biology', 70, 'Roger', 3000),

  (NULL, 80, 'Johnson', 2000);

SELECT course,

DENSE_RANK() OVER (PARTITION BY course ORDER BY SUM(mark) DESC) AS dense_rank, mark, name, score

FROM student GROUP BY course, score;

+---------+------------+------+---------+-------+

| course  | dense_rank | mark | name    | score |

+---------+------------+------+---------+-------+

| NULL    |         1 |   80 | Johnson |  2000 |

| Biology |          1 |   60 | Bilal        |  3000 |

| Maths   |          2 |   60 | Thulile    |  1515 |

| Maths   |          3 |   55 | Chun      |  1600 |

| Maths   |          1 |   60 | Pritha     |  2000 |

+---------+------------+------+---------+-------+

5 rows in set (0.001 sec)

SELECT course,

DENSE_RANK() OVER (PARTITION BY course ORDER BY (SUM(mark) * 2) DESC) AS dense_rank, mark, name, score

FROM student GROUP BY course, score;

+---------+------------+------+---------+-------+

| course  | dense_rank | mark | name    | score |

+---------+------------+------+---------+-------+

| NULL    |         1 |   80 | Johnson |  2000 |

| Biology |          1 |   60 | Bilal        |  3000 |

| Maths   |          1 |   60 | Thulile   |  1515 |

| Maths   |          1 |   55 | Chun     |  1600 |

| Maths   |          1 |   60 | Pritha    |  2000 |

+---------+------------+------+---------+-------+

5 rows in set (0.000 sec)