First questions to the data · Lesson 4 of 20

ORDER BY and LIMIT: sort and take the top

Sorting by several columns, where NULLs go, and paging with OFFSET.

9 minutes2 games

Without ORDER BY the database promises no order, even if results look sorted today. If order matters, ask for it. ASC is ascending (the default), DESC is descending.

SQL

SELECT name, city, xp
FROM students
ORDER BY city, xp DESC;

Sorted by city first, then by XP from highest to lowest inside each city. Notice Youssef (NULL) lands last in Alexandria.

LIMIT and OFFSET

Step by step1 / 5

1SELECT name, xp
▶FROM students
3WHERE xp IS NOT NULL
4ORDER BY xp DESC
5LIMIT 3;

Variables

rows

Output

…

FROM first: take every row of the table.

A query is written in one order but runs in another. Step through it.

OFFSET skips some rows first, which is how pages work: page 2 (3 per page) is LIMIT 3 OFFSET 3.

SQL

-- Page 2 of the leaderboard, 3 students per page
SELECT name, xp
FROM students
ORDER BY xp DESC
LIMIT 3 OFFSET 3;

Order the lines

Put the query's clauses in the order you write them.

Click the lines in the right order

Exercise

Get the 3 cheapest paid courses (price above zero): title and price, cheapest first.

Expected result:

title | price
SQL | 300
Python | 400
JavaScript | 450

SQL

SELECT title, price
FROM courses;

Learn more from

Played 0 of 2 games

SQL