Skip to main content

​
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      |
+------------+------------+
⌘I