Top Two Students in Each Major

Find the two students with the highest gpa in every major. In a CTE named ranked, join students to departments and number each student within their major_department_id with ROW_NUMBER() by gpa descending (ties broken by id), skipping NULL gpas. Then return department (the department name), first_name, last_name, gpa and rn for the rows numbered 1 or 2, sorted by department, then rn. Undeclared students belong to no major, and Fine Arts has only one student.

Expected result:

+------------------+------------+-----------+------+----+
| department       | first_name | last_name |  gpa | rn |
+------------------+------------+-----------+------+----+
| Biology          | Harper     | Singh     | 3.55 |  1 |
| Biology          | Isabella   | Okoro     |  3.1 |  2 |
| Business         | Abigail    | Larsen    |  3.3 |  1 |
| Business         | Mason      | Nguyen    | 3.25 |  2 |
| Computer Science | Grace      | Lee       | 3.88 |  1 |
| Computer Science | Ava        | Patel     |  3.8 |  2 |
| English          | Mia        | Haddad    |  3.8 |  1 |
| English          | Zoe        | Campbell  |  3.5 |  2 |
| Fine Arts        | Scarlett   | Johnson   |  3.6 |  1 |
| Mathematics      | Evelyn     | Park      | 3.98 |  1 |
+------------------+------------+-----------+------+----+
(showing 10 of 11 rows)

Hints4 available

Topics:

Window Functions

Code Editor

Tab to indent (Esc then Tab to move on) · Ctrl+Enter to run · Ctrl+Space to expand shortcuts (sel, ins, desc)

Results

Run your SQL to see every statement's output here, then how it compares with the expected answer.

Not sure what is in a table? SHOW TABLES lists them and DESCRIBE students shows the columns — or open the database browser on the left.