MySQL 8.0

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

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