By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (`Student` VARCHAR(6), `Grade` INTEGER, `Course` VARCHAR(16));
INSERT INTO tablename (`Student`, `Grade`, `Course`) VALUES
('JOHN', '10', 'Algebra'),
('JOHN', '7', 'Computer science'),
('ANNA', '10', 'Algebra'),
('ANNA', '10', 'Data structures'),
('JACOB', '10', 'Data structures'),
('GEORGE', '6', 'Computer science'),
('GEORGE', '10', 'Algebra'),
('GEORGE', '9', 'Data structures');
SELECT Student, GROUP_CONCAT(LPAD(Grade, 2, '0') ORDER BY Grade DESC) grades
FROM tablename
GROUP BY Student
| Student | grades |
|---|---|
| ANNA | 10,10 |
| GEORGE | 10,09,06 |
| JACOB | 10 |
| JOHN | 10,07 |
SELECT t.*
FROM tablename t
INNER JOIN (
SELECT Student, GROUP_CONCAT(LPAD(Grade, 2, '0') ORDER BY Grade DESC) grades
FROM tablename
GROUP BY Student
) g ON g.Student = t.Student
ORDER BY grades DESC, Grade DESC
| Student | Grade | Course |
|---|---|---|
| ANNA | 10 | Algebra |
| ANNA | 10 | Data structures |
| GEORGE | 10 | Algebra |
| GEORGE | 9 | Data structures |
| GEORGE | 6 | Computer science |
| JOHN | 10 | Algebra |
| JOHN | 7 | Computer science |
| JACOB | 10 | Data structures |