Chapter 8 Home | Prev: 8.7 Sorting | Next: 8.9 CRUD
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 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.
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.