Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Reporting with SQL

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Limiting the Number of Results

2:49 with Andrew Chalkley

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

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up