EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Offset
 

MySQL Offset

Roja Metla
Article byRoja Metla
EDUCBA
Reviewed byRavi Rathore

Updated May 24, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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
EDUCBA

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

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW