​
Overview

The OFFSET is a clause that skips some records from the result set.

​
Syntax

The basic syntax of the OFFSET clause is shown below:

SELECT columns
FROM table_name
OFFSET num;

The parameters and arguments from the syntax are:

  • columns: the columns to be fetched.
  • table_name: a table from which the records will be fetched.
  • OFFSET: a clause that will skip a subset of records.
    • num: the number of records to be skipped.

​
Example

1. Here, we are creating one new table called oxlafunctions using the CREATE TABLE command and inserting some values into the table using the INSERT command:

CREATE TABLE oxlafunctions  
(  
    func_name text,   
    func_sub text   
);  

INSERT INTO oxlafunctions   
VALUES
('Numeric', 'ABS'),  
('Numeric', 'CEIL'),  
('Text', 'LENGTH'),  
('Numeric', 'SQRT'),  
('Boolean', 'IF'),  
('Text', 'STRPOS'),  
('Numeric', 'FLOOR'),  
('Text', 'CONCAT'),  
('Text', 'LOWER');

2. To verify that the values have been inserted successfully, retrieve the result set using the command below: 

SELECT * FROM oxlafunctions;

+------------+------------+
| func_name  | func_sub   |
+------------+------------+
| Numeric    | ABS        |
| Numeric    | CEIL       |
| Text       | LENGTH     |
| Numeric    | SQRT       |
| Boolean    | IF         |
| Text      | STRPOS     |
| Numeric    | FLOOR      | 
| Text      | CONCAT     |
| Text      | LOWER      |
+------------+------------+

3. Use the LIMIT clause in conjunction with the OFFSET clause to skip a subset of records:

SELECT * FROM oxlafunctions
ORDER BY func_name
LIMIT 5 OFFSET 2;

In the above query:

  • The “LIMIT 5” clause is used to fetch only five records.
  • The “OFFSET 2” clause is used to skip the first two records before retrieving the result set of the limit clause.

4. You will get the following output:

+------------+------------+
| func_name  | func_sub   |
+------------+------------+
| Boolean    | IF         |
| Numeric    | SQRT       |
| Numeric    | CEIL       |
| Numeric    | ABS        | 
| Numeric    | FLOOR      |
+------------+------------+