EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Offset

MySQL Offset

By Roja MetlaRoja Metla

MySQL Offset

Introduction to MySQL Offset

We use MySQL Offset to specify the row from which we want to retrieve the data. To be precise, determine 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 combination, 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 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 Work?

Now let us see how the LIMIT and OFFSET work in 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. 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 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 IDs with 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

The example shows that the third value is the “900000” credit limit for more than one “cust_id”. We get only three rows as we have set the limit to 3. 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 IDs that start 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 with 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 the first customer id with 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 the first customer id with 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 combination, 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 with the SELECT, UPDATE, or DELETE commands. OFFSET value starts from 0 (start value).

Recommended Articles

We hope that this EDUCBA information on “MySQL Offset” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL GROUP_CONCAT()
  2. Working of MySQL CTE
  3. MySQL Limit
  4. MySQL CHECK Constraint
MICROSOFT POWER BI Training
48+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Certification Course
89+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE
97+ Hours of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Certification Course
26+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Hurry! - Any Learning Path @ $19 | OFFER ENDING IN ENROLL NOW