Rank Students Within Their City

Rank the students inside their own city rather than across the whole college: return city, first_name, last_name, gpa and city_rank, where city_rank is RANK() by gpa descending restarted for each city. Skip students whose gpa is NULL. Sort by city, then city_rank, then id.

Expected result:

+-------------+------------+-----------+------+-----------+
| city        | first_name | last_name |  gpa | city_rank |
+-------------+------------+-----------+------+-----------+
| Guelph      | Alexander  | Petrov    | 2.55 |         1 |
| Hamilton    | Isabella   | Okoro     |  3.1 |         1 |
| Hamilton    | Amelia     | Schmidt   | 3.05 |         2 |
| Hamilton    | James      | Mensah    | 2.85 |         3 |
| Hamilton    | Jack       | Thompson  |  2.2 |         4 |
| Kingston    | Mia        | Haddad    |  3.8 |         1 |
| London      | Lily       | Moreau    | 3.33 |         1 |
| London      | Benjamin   | Ivanov    |  2.7 |         2 |
| London      | Ethan      | Walsh     |  2.6 |         3 |
| Mississauga | Liam       | Nguyen    | 3.45 |         1 |
+-------------+------------+-----------+------+-----------+
(showing 10 of 27 rows)

Hints3 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.