SQL Reference - OFFSET

Example

SELECT * FROM Orders
LIMIT 5 OFFSET 3;

Output

| OrderId | CustomerId | EmployeeId | OrderDate | ProductId |
|---------|------------|------------|-----------|-----------|
| 4       | 103        | 2          | 2021-09-04| 30        |
| 5       | 105        | 1          | 2021-09-05| 20        |
| 6       | 108        | 3          | 2021-09-06| 10        |
| 7       | 109        | 4          | 2021-09-07| 40        |
| 8       | 112        | 2          | 2021-09-08| 50        |

Explanation

In this example, OFFSET 3 instructs MySQL to disregard the first 3 rows and LIMIT 5 limits the output to five rows. Therefore the query displays rows 4 to 8 from the Orders table.

Example

SELECT * FROM orders
ORDER BY order_id
LIMIT 5
OFFSET 10;

Output

 order_id | customer  | order_date | amount
----------+-----------+------------+--------
     11   | John Doe  | 2018-07-14 |  95.20
     12   | Jane Doe  | 2018-07-15 | 120.55
     13   | Jim Smith | 2018-07-16 |  65.70
     14   | Jake Doe  | 2018-07-17 |  85.20
     15   | Jill Doe  | 2018-07-18 |  45.67
(5 rows)

Explanation

The example above demonstrated how to obtain records from the orders table, ordered by order_id. It limited the output to 5 records, but also used an OFFSET of 10, which means it skipped the first 10 records in the result set and returned the next 5.

Example

SELECT
   ProductName,
   Price
FROM
   Products
ORDER BY
   Price
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Output

ProductName	Price
Product 11	11
Product 12	12
Product 13	13
Product 14	14
Product 15	15
Product 16	16
Product 17	17
Product 18	18
Product 19	19
Product 20	20

Explanation

The provided SQL query implements OFFSET to skip the first 10 records of the Products table, then uses FETCH NEXT to return the next 10 records. The data is sorted by Price. As a result of the query, records 11 through 20 are displayed.

Example

SELECT *
FROM Employees
ORDER BY employee_id
OFFSET 5 ROWS;

Output

| EMPLOYEE_ID | FIRST_NAME  | LAST_NAME   |
|-------------|-------------|-------------|
| 6           | Aaron       | Adams       |
| 7           | Anna        | Anderson    |
| 8           | Alex        | Arnold      |
| 9           | Addison     | Atkinson    |
| 10          | Ashley      | Armstrong   |

Explanation

In the example provided, the SQL statement selects all rows from the Employees table and orders them by the employee_id. The OFFSET clause then skips the first 5 rows in the ordered result-set. Therefore, the output displays the data starting from the 6th row.

Example

SELECT * FROM Students
ORDER BY Score DESC
LIMIT 5 OFFSET 3;

Output

| ID | StudentName | Score |
|----|-------------|-------|
| 7  | Alice       | 85    |
| 8  | Bob         | 82    |
| 11 | Charlie     | 80    |
| 12 | Dave        | 78    |
| 14 | Ethan       | 75    |

Explanation

The OFFSET keyword is used in conjunction with the LIMIT keyword to skip a number of rows before returning the result set. In this example, the query is skipping the first 3 rows and retrieving the next 5 rows from the Students table. These rows are ordered by the Score in descending order.