Lesson 5 of 17 · First Queries
LIMIT and DISTINCT
Return only the first rows with LIMIT, build top-N lists with ORDER BY, page through results with OFFSET and remove duplicate values with DISTINCT.
- 7 min read
- Beginner
- Reviewed
- How to return only a few rows with LIMIT
- How to build a top-N list
- How to skip rows with OFFSET
- How to remove duplicates with DISTINCT
Too many rows
Real tables have millions of rows. You rarely want them all on your screen. LIMIT tells the database to stop after a certain number.
SELECT * FROM students LIMIT 3;
This is the standard way to take a quick look at a table you have never seen before.
Top-N lists
LIMIT becomes really useful together with ORDER BY. Sort first, then keep the top few.
SELECT name, age FROM students ORDER BY age DESC LIMIT 3;
These are the three oldest students. Change DESC to ascending order and you get the three youngest.
LIMIT is always the last clause of a query.
Skipping rows with OFFSET
OFFSET skips a number of rows before LIMIT starts counting. Together they show a list one page at a time.
-- Page 2, with 4 students per page SELECT id, name FROM students ORDER BY id LIMIT 4 OFFSET 4;
Rows 1 to 4 are skipped, and rows 5 to 8 are returned. For page 3 you would write OFFSET 8.
Removing duplicates with DISTINCT
Which countries do our students come from? Selecting the column gives one row per student, so India appears twice.
SELECT country FROM students ORDER BY country;
DISTINCT removes the repeats. Each different value appears once.
SELECT DISTINCT country FROM students ORDER BY country;
Notice that NULL, the missing country, counts as one of the values and is listed too.
DISTINCT on several columns
With more than one column, DISTINCT removes rows where the whole combination is repeated.
SELECT DISTINCT category, hours FROM courses ORDER BY category;
Programming appears twice, because its two courses have different lengths, so the two rows are not identical.
Putting it together
A query can now have five clauses. They are always written in this order.
SELECT DISTINCT country FROM students WHERE age >= 22 ORDER BY country LIMIT 5;
Common mistakes
Find the three youngest students. Show their name and age, youngest first.
-- Show the name and age of the three YOUNGEST students, -- youngest first
What does SELECT DISTINCT country FROM students; return?
Summary
LIMIT nreturns at mostnrows and goes at the end of the query.ORDER BY ... LIMIT ngives a top-N list.OFFSETskips rows, which is how paging works.DISTINCTremoves duplicate rows from the result.
You have finished the first module. Next, you will learn to summarise many rows into a single answer.
Finished reading? Tick it off to track your progress.