Sorting, Limiting and Removing Duplicates

Introduction

A database does not promise to return rows in any particular order unless you ask. This lesson shows how to sort results, take the first few, and remove repeated values.

ORDER BY

ORDER BY sorts the result. The default is ascending (smallest first, or A to Z).

Add DESC for descending order, which gives the biggest first.

You can sort by more than one column. Rows that tie on the first column are sorted by the second.

WHERE and ORDER BY together

The order of the clauses matters: SELECT, FROM, WHERE, then ORDER BY.

LIMIT and OFFSET

LIMIT returns only the first rows. Together with ORDER BY it answers "top N" questions.

OFFSET skips rows first. This is how websites show "page 2" of results.

Always pair LIMIT with ORDER BY. Without sorting, "the first 3 rows" can be any 3.

DISTINCT

Several products share a category. DISTINCT returns each value once.

Look at where the NULL sorts. In SQLite, NULL counts as the smallest value, so it comes first in ascending order.

Try it yourself

Show the two cheapest products that are in stock more than 50 times, with their name, price and stock.

Summary

• ORDER BY column sorts ascending. DESC reverses it. You can sort by several columns.
• LIMIT n keeps the first n rows, and OFFSET skips rows first.
• DISTINCT removes duplicate rows from the result.
• Clause order is SELECT, FROM, WHERE, ORDER BY, LIMIT.