Sorting and Limiting Results

After learning how to choose rows with WHERE and learning how to work with NULL, another common need is to decide how rows should be arranged and how many of them should be shown.

For example:

• Which exercises are due first?
• Which are the five latest submissions?
• Which different statuses appear in the table?

SQL provides tools for these questions: ORDER BY, LIMIT, and DISTINCT.

SELECT DISTINCT column_name
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT number;

Sorting Rows

Suppose the question is: Which exercises should be shown from earliest deadline to latest?

Then the order becomes part of the question.

SELECT
  id,
  title,
  deadline_at
FROM exercises
ORDER BY deadline_at;

Read this as: show the exercise rows ordered by deadline_at, from the earliest deadline to the latest.

When SQL sorts from smaller values to larger values, it is called ascending order. With dates and timestamps, that means earlier to later. With text, it often means alphabetical order. ASC is short for ascending.

Ascending order is the default, so ORDER BY deadline_at means the same thing as ORDER BY deadline_at ASC.

If the question is instead: Which exercises have the latest deadlines?

then we want the order reversed. For that, use DESC.

SELECT
  id,
  title,
  deadline_at
FROM exercises
ORDER BY deadline_at DESC;

DESC is short for descending. With dates and timestamps, that means later to earlier. With numbers, it means larger to smaller.

Sometimes one ordering column is not enough. Suppose several exercises have the same deadline. Then we may still want a clear order among those equal-deadline rows.

SELECT
  id,
  title,
  deadline_at
FROM exercises
ORDER BY deadline_at DESC, title ASC;

Here deadline_at decides the main order. If several rows have the same deadline_at, then title decides their order alphabetically.

If you sort by only one column, rows with equal values in that column may still appear in any relative order.

Limiting Result Size

Sometimes the question is not “show all matching rows,” but:

• Which are the five most recent rows?
• Which are the first three rows in alphabetical order?

Then sorting and limiting are often used together. For example, the following query answers the question: Which are the five most recent exercises?

SELECT
  id,
  title
FROM exercises
ORDER BY deadline_at DESC
LIMIT 5;

This query works in two steps:

• ORDER BY deadline_at DESC arranges the rows from latest deadline to earliest
• LIMIT 5 keeps only the first five rows from that ordered result

A common beginner mistake is to treat LIMIT 5 as if it means “the five latest rows” by itself. Without ORDER BY, however, the database has not been told what “first” should mean. So LIMIT 5 without ORDER BY just means “some five rows”.

If the order matters, always state it explicitly.

Duplicates and DISTINCT Values

Some questions are not about every row. They are about which different values appear at all. For example: Which submission statuses exist in the table?

If you only care about unique values, DISTINCT can help.

SELECT DISTINCT status
FROM submissions;

Read this as: show each different status value once.

In an ordinary SELECT, one result row often represents one stored row. Here one result row represents one distinct status value that appears in the table: DISTINCT removes repeated values from what you selected.

If you need to sort or limit the distinct values, you can combine DISTINCT with ORDER BY and LIMIT. For example, the following query answers the question: Which are the three most recent distinct exercise deadlines?

SELECT DISTINCT deadline_at
FROM exercises
ORDER BY deadline_at DESC
LIMIT 3;

Check Your Understanding

1. What does ORDER BY deadline_at DESC mean in ordinary language?
2. Why is LIMIT often useful while exploring data?
3. When might DISTINCT be useful in a query result?

SQL Practice

Complete the SQL sequence below to practice sorting rows, limiting results, and returning only distinct values.