The top three

Show the three students with the highest marks. If there are fewer than three students, show them all.

What to build

A query that shows the three best students.

Requirements

• Sort by marks from highest, then by name.
• Keep only the first three rows.
• Show everyone when there are fewer than three students.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);

Output

Columns name and marks, from the highest marks down. Students with equal marks are in alphabetical order of name, and that order decides who is in the top three. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Students may share the same marks.

Example

When a test starts with:

INSERT INTO students VALUES
  (1, 'Rahim', 'Dhaka', 85),
  (2, 'Karim', 'Sylhet', 72),
  (3, 'Nadia', 'Dhaka', 91),
  (4, 'Tania', 'Dhaka', 55),
  (5, 'Sumon', 'Khulna', 88);

your SQL should print:

name   marks
-----  -----
Nadia  91
Sumon  88
Rahim  85
(3 rows)

Why it works

LIMIT keeps the first rows of the sorted result, and sorting by name too makes ties come out in a fixed order.

Examples

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);
INSERT INTO students VALUES
  (1, 'Rahim', 'Dhaka', 85),
  (2, 'Karim', 'Sylhet', 72),
  (3, 'Nadia', 'Dhaka', 91),
  (4, 'Tania', 'Dhaka', 55),
  (5, 'Sumon', 'Khulna', 88);

name   marks
-----  -----
Nadia  91
Sumon  88
Rahim  85
(3 rows)

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);
INSERT INTO students VALUES
  (1, 'Rina', 'Dhaka', 70),
  (2, 'Imran', 'Dhaka', 80);

name   marks
-----  -----
Imran  80
Rina   70
(2 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT name, marks
FROM students
ORDER BY marks DESC, name
LIMIT 3;

Lesson: Sorting, limiting and DISTINCT →