519. Topper in Each Subject
HardWindow Functions
For each subject return its title, the topper's name and score (all toppers if tied), ordered by title then name.
students
| id | name | class | city | dob |
|---|---|---|---|---|
| 1 | Arun | 10A | Chennai | 2010-05-14 |
| 2 | Bala | 10A | Madurai | 2010-08-02 |
| 3 | Charu | 10B | Chennai | 2009-12-25 |
| 4 | Deepa | 10B | Coimbatore | 2010-03-30 |
| 5 | Ezhil | 10A | Chennai | 2010-11-11 |
| 6 | Farah | 10C | NULL | 2010-01-19 |
subjects
| id | title | max_marks |
|---|---|---|
| 1 | Maths | 100 |
| 2 | Science | 100 |
| 3 | English | 100 |
| 4 | Tamil | 100 |
marks
| student_id | subject_id | score |
|---|---|---|
| 1 | 1 | 95 |
| 1 | 2 | 88 |
| 1 | 3 | 76 |
| 2 | 1 | 67 |
| 2 | 2 | 72 |
| 2 | 3 | 81 |
| 3 | 1 | 99 |
| 3 | 2 | 94 |
| 3 | 3 | 90 |
| 4 | 1 | 45 |
| 4 | 2 | 58 |
| 4 | 3 | 62 |
| 5 | 1 | 88 |
| 5 | 2 | 91 |
| 5 | 3 | 85 |
| 1 | 4 | 90 |
| 3 | 4 | 85 |
Your query runs on a fresh copy of these tables (SQLite). Column names in your result do not need to match.
Loading the MyLoginn IDE…