Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Databases can store massive amounts of data, and often you don't want to bring back all of the results. It's slow and it affects the performance for other users. In most cases you only want a certain subset of the rows.
SQLite, PostgreSQL and MySQL
To limit the number of results returned, use the LIMIT keyword.
SELECT <columns> FROM <table> LIMIT <# of rows>;
MS SQL
To limit the number of results returned, use the TOP keyword.
SELECT TOP <# of rows> <columns> FROM <table>;
Oracle
To limit the number of results returned, use the ROWNUM keyword in a WHERE clause.
SELECT <columns> FROM <table> WHERE ROWNUM <= <# of rows>;
Cheat Sheets
Databases can store massive
amounts of data, and
0:00
often you don't want to bring
back all of the results.
0:04
It's slow to do something like that,
it affects the performance for
0:07
other users, and in most cases
you only want a subset of rows.
0:10
Not all of them are needed.
0:14
Imagine your company,
0:16
an e-commerce site, launched nine
different marketing campaigns using email,
0:17
television, banner ads, Google ads, and
a variety of other marketing channels.
0:22
The marketing team wants to know which of
these campaigns produced the most revenue.
0:28
They want to find out the top three
effective marketing campaigns.
0:33
Fortunately your website tracks
all of this information and
0:37
stores it in a database.
0:41
Let's take a look in our database to
see how we can answer this question.
0:43
Let's have a look at the campaigns table.
0:47
We have an ID column, the name of
the campaign and the number of sales.
0:50
Let's start with a general select
staff from campaign statement.
0:56
We want to order the results
by the number of sales but
1:01
in a descending order, meaning from the
largest to the smallest number of sales.
1:05
Remember the marketing team only
wants the top three results.
1:12
We could retrieve all the results and
then manually pick off the top three or
1:16
just give the full list to the person
in the marketing department,
1:20
which may be a bit distracting to
whoever this report is intended for.
1:23
Luckily SQL provides a way to limit
the amount of results returned.
1:28
To retrieve just a handful
of the total of results,
1:33
we use the LIMIT keyword followed by
the number of rows we want retrieved.
1:36
When we run this query now, we get the top
three campaigns that generate sales.
1:43
To recap,
you can start any query you'd like and
1:49
end it with the LIMIT keyword with
the number of rows you'd like returned.
1:53
You can also use it with queries with
no conditions and with any number
1:57
of conditions and including other
SQL keywords like ORDER BY.
2:03
The LIMIT keyword with the number
of rows you want returning
2:09
must be at the end of each query.
2:12
Putting LIMIT anywhere but at the end of
the statement generates a syntax error.
2:15
You've already limited results
with the WHERE clause, but
2:21
now you can limit results
with the LIMIT keyword.
2:25
With WHERE clauses you need to know
specifically the conditions you want to
2:29
filter by, whereas with LIMIT,
2:32
you're just concerned with
the number of records returned.
2:34
LIMIT isn't available
in all SQL databases.
2:38
Other databases can use
a different keyword and syntax.
2:41
I will include some code examples
in the teacher's notes below.
2:45
You need to sign up for Treehouse in order to download course files.
Sign up