This lecture covers the LIMIT clause to restrict the number of rows returned.
Sometimes your queries can return thousands or even millions of rows. Displaying all of them at once can be slow and often isn’t very useful. The LIMIT clause allows you to restrict the number of rows returned by your query.
This is incredibly useful for things like finding the “Top 10” of something, or for creating pages of results (pagination).
Getting the Top N Rows
The basic syntax of the LIMIT clause is simple. You just add LIMIT N to the end of your query, where N is the number of rows you want.
Let’s say we only want to see the first two contacts that were entered into our `Contacts` table. We can do this by limiting the result to 2.
SELECT * FROM Contacts LIMIT 2;This will return only the first two rows it finds, which will likely be Jane Doe and John Smith.
Combining `LIMIT` with `ORDER BY`
The real power of LIMIT is unlocked when you combine it with ORDER BY. This allows you to find the top or bottom records based on a certain order.
For example, let’s find the single most recently added contact. We can do this by ordering the contacts by their ID in descending order (newest first) and then limiting the result to 1.
SELECT * FROM Contacts ORDER BY ContactID DESC LIMIT 1;This query will always return just one row: the last contact that was added to the table.
Here’s another example: let’s find the first two contacts alphabetically by first name.
SELECT * FROM Contacts ORDER BY FirstName ASC LIMIT 2;Pagination: Using `LIMIT` with `OFFSET`
LIMIT can also be used to create pages of results. Imagine you have 1000 contacts and you want to display them 10 per page. The OFFSET keyword tells the database how many rows to skip before it starts returning results.
The syntax is LIMIT N OFFSET M, which means “give me N rows, but skip the first M rows”.
- Page 1: Get the first 10 contacts (skip 0).
SELECT * FROM Contacts ORDER BY ContactID LIMIT 10 OFFSET 0;- Page 2: Get the next 10 contacts (skip the first 10).
SELECT * FROM Contacts ORDER BY ContactID LIMIT 10 OFFSET 10;- Page 3: Get the next 10 contacts (skip the first 20).
SELECT * FROM Contacts ORDER BY ContactID LIMIT 10 OFFSET 20;This technique is the foundation of how pagination is implemented in almost every application that deals with large amounts of data.
In the Next Lecture…
What if your table contains duplicate data? For example, what if you have multiple contacts who live in the same city? In the next lecture, we will learn how to retrieve only the unique values from a column using the DISTINCT keyword.
