Chapter 3 Home | Prev: 3.7 Sorting | Next: 3.9 CRUD

3.8 - Limiting Results and Paging

Limiting Rows Returned

Sometimes data resultant sets are very large, but we might only be interested in the last few records, or the first few records.  We can use the OFFSET and FETCH commands in Oracle or the TOP command in MS SQL to determine which rows in a set are to be returned.

If we wanted to see the first 10 orders in the database that were received we could write:

    SELECT * 
    FROM orders
    ORDER BY orderdate
    FETCH NEXT 10 ROWS ONLY;

    SELECT TOP 10 * 
    FROM orders
    ORDER BY orderdate;
-- OR
    SELECT * 
    FROM orders
    ORDER BY orderdate
    OFFSET 0 rows  -- required in MS SQL for fetch to work
    FETCH NEXT 10 ROWS ONLY;

If we wanted to see the last 10 orders then we would simply change the sort order to DESC.

Paging

It is very common for website to have search engines built in and often a basic search may return many rows, sometimes thousands. It is unrealistic to show all the results on the page at once and equally unrealistic to load all the data from the database to the website only to show some of them. Therefore, it is important to have a way to query the database only retrieving those rows that are to be displayed. This is often the case in paging, where Next and Previous buttons would allow the user to move forward or backwards some many results (typically 10 or so).

If we want to skip a few records to see only some in the middle somewhere, we can use the OFFSET clause.

    -- Page 3 of results with 10 records per page
    SELECT * FROM orders
    ORDER BY orderdate
    OFFSET 20 ROWS
    FETCH NEXT 10 ROWS ONLY;

skipping the first 20 orders, this query shows the next 10 orders after those skipped. i.e.: this would be page 3 in the results if 10 results were shown per page.

ROWNUM (Oracle Only)

Rownum is a builtin value in all query results that indicates the Rown Number of the results.  Therefore you can use ROWNUM to limit the number of rows returned of return exact rows.

SELECT * FROM orders
WHERE rownum <= 5;
    -- returns the first 5 rows
SELECT * FROM orders
WHERE rownum BETWEEN 10 and 20
    -- returns the row 10 through 20 (11 rows)

You can also use ROWNUM in calculations or to display.   If you were building a ranking results, than you would want 1, 2, 3 to represent position.

SELECT rownum num, rownum*rownum AS sqr, rownum*rownum*rownum AS cub
FROM orders
WHERE rownum < 10;