EDUCBA

Home » Data Science » Data Science Tutorials » MySQL Tutorial » MySQL Offset

MySQL Offset

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

MySQL Offset-1.1

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.

MySQL Offset-1.2

Output for the above query can be seen in the diagram:

MySQL Offset-1.3

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: 

MySQL Offset-1.4

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;

Output:

MySQL Offset-1.5

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:

Output-1.6

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:

Output-1.7

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:

Output-1.8

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:

Output-1.9

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 –

  1. MySQL GROUP_CONCAT()
  2. MySQL CTE
  3. MySQL Limit
  4. MySQL CHECK Constraint
1 Shares
Share
Tweet
Share