Velamuvi AIAcademy

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
In this lesson you will learn
  • 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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
-- 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.

Try it yourselfSQL
SELECT country
FROM students
ORDER BY country;

DISTINCT removes the repeats. Each different value appears once.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
SELECT DISTINCT country
FROM students
WHERE age >= 22
ORDER BY country
LIMIT 5;

Common mistakes

Practice exercise

Find the three youngest students. Show their name and age, youngest first.

Your solutionSQL
-- Show the name and age of the three YOUNGEST students,
-- youngest first
Knowledge check

What does SELECT DISTINCT country FROM students; return?

Summary

  • LIMIT n returns at most n rows and goes at the end of the query.
  • ORDER BY ... LIMIT n gives a top-N list.
  • OFFSET skips rows, which is how paging works.
  • DISTINCT removes 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.