The second page of results

Websites show long lists one page at a time. Show page 2.

What to build

A query that returns the second page of a ranking, with three students per page.

Requirements

• Rank by marks from highest, and by name when marks are equal.
• Skip the first three students and return the next three.
• Return name and marks.

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, in ranking order. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Marks may be NULL, and SQLite sorts NULL as the smallest value.

Example

When a test starts with:

INSERT INTO students VALUES (1, 'Rahim', 'Dhaka', 85), (2, 'Karim', 'Sylhet', 72), (3, 'Nadia', 'Dhaka', 91), (4, 'Tania', 'Khulna', 64), (5, 'Sumon', 'Rajshahi', 85), (6, 'Rina', 'Dhaka', 79), (7, 'Mitu', 'Sylhet', 50);

your SQL should print:

name   marks
-----  -----
Rina   79
Karim  72
Tania  64
(3 rows)

Why it works

OFFSET skips rows of the sorted result, and LIMIT keeps the next ones. Because the sort includes the name, every page always shows the same students.

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', 'Khulna', 64), (5, 'Sumon', 'Rajshahi', 85), (6, 'Rina', 'Dhaka', 79), (7, 'Mitu', 'Sylhet', 50);

name   marks
-----  -----
Rina   79
Karim  72
Tania  64
(3 rows)

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);
INSERT INTO students VALUES (1, 'A', 'x', 10), (2, 'B', 'x', 20), (3, 'C', 'x', 30), (4, 'D', 'x', 40);

name  marks
----  -----
A     10
(1 row)

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 OFFSET 3;

Lesson: Sorting, limiting and DISTINCT →