SQSQL · Lesson 3 of 8

ORDER BY, LIMIT & DISTINCT

Rows in a table have no guaranteed order. If you want the top 10 students by grade, you need to say so explicitly — that's ORDER BY and LIMIT.

SQL

-- Sort ascending (default):
SELECT name, grade FROM students ORDER BY grade;

-- Sort descending:
SELECT name, grade FROM students ORDER BY grade DESC;

-- Sort by several columns (ties broken by the next one):
SELECT name, age, grade FROM students
ORDER BY age DESC, grade DESC;

-- Only the first N rows:
SELECT name, grade FROM students
ORDER BY grade DESC
LIMIT 3;

-- Skip rows with OFFSET (page 2 of results):
SELECT name FROM students
ORDER BY name
LIMIT 10 OFFSET 10;

DISTINCT removes duplicate rows from the result. Ask 'which ages appear in the table?' and you don't want 17 listed five times.

SQL

SELECT DISTINCT age FROM students;

-- DISTINCT applies to the whole selected row:
SELECT DISTINCT age, grade FROM students;
-- (only removes rows where BOTH values repeat)

◆ Note
LIMIT/OFFSET is how apps implement pagination — page 3 with 20 items per page is LIMIT 20 OFFSET 40. Beware: without ORDER BY, pagination is meaningless, because the database is free to return rows in any order it likes.