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

Paging Through Results

2:33 with Andrew Chalkley

Limiting just by the top set of results is a fine thing, but let's say you wanted to generate a multi page report. Having a blog archive or listing search results in batches of 50 is where you want paging.

SQLite, PostgreSQL and MySQL

To page through results you can either use the OFFSET keyword in conjunction with the LIMIT keyword or just with LIMIT alone.

SELECT <columns> FROM <table> LIMIT <# of rows> OFFSET <skipped rows>;
SELECT <columns> FROM <table> LIMIT <skipped rows>, <# of rows>;

MS SQL and Oracle

To page through results you can either use the OFFSET keyword in conjunction with the FETCH keyword. Cannot be used with TOP.

SELECT <columns> FROM <table> OFFSET <skipped rows> ROWS FETCH NEXT <# of rows> ROWS ONLY;

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

    Limiting just by the top set of results is a fine thing. 0:00
    But what if we wanted to generate a multi-page report? 0:05
    Let's say we were running a blog and 0:08
    we want to present ten posts on a single page, going through an archive. 0:10
    Or in an e-commerce site we want to show orders in batches of 50. 0:15
    Let's take a look on how we do that. 0:20
    Here we have a limiting query for orders. 0:23
    Remember, you can use the WHERE causes and 0:26
    the ORDER BY clauses before the limit 50 to change the order of the results. 0:29
    But, let's just focus on whatever order the database returns the results in 0:35
    by default first. 0:39
    There are actually hundreds of orders in this table and 0:42
    we can bring them back 50 at a time so we're not overwhelmed with a wall of data. 0:45
    We want to start at the 51st row for 0:51
    our second set of data, and then the 101st row for our third set. 0:54
    We want to skip over rows in increments of 50. 1:00
    In other words, we want to use an offset. 1:04
    And that's the keyword we use. 1:08
    Offset, followed by the number you want to offset by. 1:10
    In our case it's 50 because we want to start after the 50th record. 1:14
    When we run this, we see it starts at the 51st row, and 1:20
    it goes all the way up to the 100th row. 1:24
    When we replace the off-set of 50 to 100 and we run this query, 1:28
    it starts at the 101st row, and it goes all the way up to the 150th. 1:33
    So in general, you start with your limiting query, and then you include 1:39
    the keyword OFFSET, followed by the number of rows you want to skip. 1:43
    There is a shorthand of this that you may see where the keyword of 1:48
    OFFSET is removed and the rows skipped is specified first, 1:51
    followed by a comma, and then the number of rows that you want to limit by. 1:55
    I find this syntax harder to remember than using the offset keyword, so 2:01
    I tend to use the former with the keyword offset. 2:05
    If you can be more explicit with your queries, it's better to be that way. 2:09
    Other people will thank you for it, and 2:13
    you'll thank yourself if you come back to that query at a later day. 2:15
    Once again, while the limit keyword is implemented in several databases, 2:20
    not all of them have this offset keyword feature. 2:25
    Check the teacher's notes to see how to do this paging in other databases. 2:28

    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