Class Rank

Scholarship eligibility at Fairhaven University is decided by class standing rather than by grade point average directly, because the committee wants a fixed number of awards each year regardless of how the cohort happens to score. Every student needs a place in the ordering, including the ones who will not qualify — the committee reads the whole list.

Two students on the same grade point average have the same standing; the committee will not put one above the other on a difference that does not exist. When that happens the next place along is skipped, so a cohort can read 1, 2, 2, 4.

students — one row per enrolled student. gpa is the grade point average already on file for that student, on a 0-4 scale.

The table already exists in the database — there is nothing to create or load.

Task: Write a query that returns one row per student, with name, gpa and their standing in the cohort as class_rank — 1 for the highest grade point average, 2 for the next, and so on. Students on the same grade point average share a standing and the following place is skipped. Return the rows best standing first, students sharing a standing separated alphabetically by name.

Example output

Shape only — these students and averages are invented. Two of them are level, so they share standing 2 and standing 3 goes unused: