Introduction to MySQL Offset
MySQL Offset is used to specify from which row we want the data to retrieve. To be precise, specify which row to start retrieving from. Offset is used along with the LIMIT. Here, LIMIT is nothing but to restrict the number of rows from the output. In combine, when you use LIMIT with the offset it means that we are trying to retrieve data from the given offset to the limit value. We can use this along with the SELECT, UPDATE or DELETE command.
Syntax:
select * from <table_name> limit [row_count] offset [offset_value];
or
select * from <table_name> limit [offset_value,] [row_count];
In this syntax:
- The [offset_value] specifies the offset of the first row to return. The offset of the first row is 0, not 1.
- The [row_count] specifies the maximum number of rows to return.
How does MySQL Offset Works?
Now let us see how the LIMIT and OFFSET works in the MySQL along with the example:
create table Test(id integer, message varchar(100));
insert into Test(id, message) values(1, "Hello, Hi I am Fred");
insert into Test(id, message) values(4, "Fred received message");
insert into Test(id, message) values(3, "Articles has been checked");
insert into Test(id, message) values(2, "Offset example");
insert into Test(id, message) values(7, "limit example");
insert into Test(id, message) values(8, "Example");
select * from Test;
Output:
select * from Test limit 3 offset 2;
The above query is to get data from the offset value “2” and restrict the data to the specified “2” rows value of LIMIT.
Output for the above query can be seen in the diagram:
Examples of MySQL Offset
Now let us see real time examples and apply the LIMIT and OFFSET.
Create the table as below:
CREATE TABLE BANK_CUST_DATA
(
CUST_ID INT,
BANK_ID VARCHAR(10),
BANK_NAME VARCHAR(10),
BANK_IFSC VARCHAR(20),
ACCOUNT_AMT INT,
CREDIT_CARD_AVAILABILITY VARCHAR(2),
CREDIT_LIMIT INT
);
Insert data into the table:
INSERT INTO BANK_CUST_DATA VALUES (343,'SBI_12','SBI','UTX1000546', 900000,'Y',1200000 );
INSERT INTO BANK_CUST_DATA VALUES (263,'SBI_12','SBI','UTX1000546', 120000,'Y',987600 );
INSERT INTO BANK_CUST_DATA VALUES (113,'SBI_12','SBI','UTX1000546', 876543,'Y',765000 );
INSERT INTO BANK_CUST_DATA VALUES (893,'SBI_12','SBI','UTX1000546', 987654,'Y',890000 );
INSERT INTO BANK_CUST_DATA VALUES (983,'SBI_12','SBI','UTX1000546', 789654,'Y',900000 );
INSERT INTO BANK_CUST_DATA VALUES (583,'SBI_12','SBI','UTX1000546', 589654,'Y',900000 );
INSERT INTO BANK_CUST_DATA VALUES (783,'SBI_12','SBI','UTX1000546', 889654,'Y',900000 );
INSERT INTO BANK_CUST_DATA VALUES (383,'SBI_12','SBI','UTX1000546', 389654,'N',0 );
INSERT INTO BANK_CUST_DATA VALUES (183,'SBI_12','SBI','UTX1000546', 289654,'N',0 );
INSERT INTO BANK_CUST_DATA VALUES (883,'SBI_12','SBI','UTX1000546', 189654,'N',0 );
select * from BANK_CUST_DATA;
Output:
Now let us find the top 3 customer id that have the highest credit_limit using the limit and offset.
SELECT
CUST_ID,
BANK_ID,
CREDIT_LIMIT
FROM
BANK_CUST_DATA
ORDER BY CREDIT_LIMIT DESC
LIMIT 0, 3;
12 Online Courses | 10 Hands-on Projects| 92+ Hours| Verifiable Certificate of Completion
4.5
View Course
Output:
Here in the example, you can see that the third value is “900000” credit limit we have for more than one “cust_id”. As we have set the limit to 3 we get only three rows. Row for “900000” can be randomized for each execution of the query to restrict we can apply the RANK function here for the table and get the data.
Now let us find the 3 customer id that starting from the second-highest “credit_limit” using the limit and offset.
SELECT
CUST_ID,
BANK_ID,
CREDIT_LIMIT
FROM
BANK_CUST_DATA
ORDER BY CREDIT_LIMIT DESC
LIMIT 1, 3;
Output:
Here we are getting the second-highest “credit_limit” with limit 3;
Now let us find the 3 customer id that has the lowest “credit_limit” using the limit and offset.
SELECT
CUST_ID,
BANK_ID,
ACCOUNT_AMT,
CREDIT_CARD_AVAILABILITY,
CREDIT_LIMIT
FROM
BANK_CUST_DATA
ORDER BY CREDIT_LIMIT ASC
LIMIT 0, 3;
Output:
Now let us find only first customer id that has the highest “credit_limit” using the limit and offset.
SELECT
CUST_ID,
BANK_ID,
ACCOUNT_AMT,
CREDIT_CARD_AVAILABILITY,
CREDIT_LIMIT
FROM
BANK_CUST_DATA
ORDER BY CREDIT_LIMIT DESC
LIMIT 0, 1;
Output:
Now let us find only first customer id that have the lowest “credit_limit” using the limit and offset.
SELECT
CUST_ID,
BANK_ID,
ACCOUNT_AMT,
CREDIT_CARD_AVAILABILITY,
CREDIT_LIMIT
FROM
BANK_CUST_DATA
ORDER BY CREDIT_LIMIT ASC
LIMIT 0, 1;
Output:
Conclusion
MySQL Offset is used to specify from which row we want the data to retrieve. To be precise, specify which row to start retrieving from. Offset is used along with the LIMIT. Here, LIMIT is nothing but to restrict the number of rows from the output. In combine, when you use LIMIT with the offset it means that we are trying to retrieve data from the given offset to the limit value. We can use this along with the SELECT, UPDATE or DELETE command. OFFSET value start from 0 (start value).
Recommended Articles
This is a guide to MySQL Offset. Here we also discuss the introduction and how does mysql offset works? along with different examples and its code implementation. You may also have a look at the following articles to learn more –
