Sorting, Limiting and Removing Duplicates

ORDER BY

SELECT name, score
FROM students
ORDER BY score DESC;   -- highest first

SELECT name, score
FROM students
ORDER BY course ASC, score DESC;   -- sort by course, then score within each course

ASC (ascending, low to high) is the default if you don’t specify a direction. You can sort by multiple columns at once, and each one can have its own direction — the second column only comes into play to break ties within the first.

LIMIT

SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 5;   -- top 5 scorers

LIMIT without ORDER BY is nearly meaningless — without a defined sort order, the database is free to return any 5 rows it happens to reach first, which is rarely what you actually want. Always pair LIMIT with an ORDER BY when the specific rows you get back matter.

Pagination with OFFSET

SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 10 OFFSET 10;   -- skip the first 10, return the next 10 ("page 2")

This is exactly how a real application implements pagination — page 1 uses OFFSET 0, page 2 uses OFFSET 10, and so on, always keeping the same ORDER BY so the page boundaries stay consistent between requests.

DISTINCT

SELECT DISTINCT course
FROM students;   -- every course name, once each

SELECT DISTINCT course, score >= 90 AS is_high_scorer
FROM students;   -- distinct combinations of multiple columns

These clauses cover almost every “top N” or “unique list of” question you’ll be asked to answer with data — a huge share of real-world SQL work is some combination of filter, sort, and limit.