This article will be looking into LIMITS with OFFSET, including the syntax, using examples.
Table of Contents
- MySQL select rows by range using LIMIT with OFFSET
- MySQL select rows by range using Row_Number() function
Let us get started by making the data. We will be creating a table customer_name_city and inserting rows into it.
CREATE TABLE customer_name_city ( customer_id INT auto_increment, customer_name VARCHAR(255), customer_city VARCHAR(255), primary key (customer_id) ); INSERT INTO customer_name_city (customer_name, customer_city) VALUES("Gustav","Santiago"), ("Henric","Boston"), ("Richa","Santiago"), ("Margit","Santiago"), ("Henric","Los Angeles"), ("Henric","Austin"), ("Richa","Washington"), ("Henric","Washington"), ("Rohit","Boston"), ("Barbara","Washington"), ("Susan","Boston"), ("Richard","Chicago"), ("Daniel","Chicago"), ("Donald","Los Angeles"), ("Anthony","Boston"), ("Melissa","Chicago"), ("Rebecca","Los Angeles"), ("Daniel","Chicago"), ("Ramya","El Paso"), ("Arjun","Portland"), ("William","Chicago"), ("Oliver","Portland"), ("Ryan","Austin");
Let us see what got into the table customer_name_city by executing:
SELECT * FROM customer_name_city;

MySQL select rows by range using LIMIT with OFFSET
LIMIT clause is used to select a limited number of rows from a SELECT statement.
Syntax:-
Let us look into the syntax of the LIMIT clause with OFFSET.
Frequently Asked:
- What are triggers in MySQL
- MySQL select row count [Everything around count()]
- LIMIT with OFFSET in MYSQL
- Mysql update set multiple columns
SELECT column1, column2... / * FROM your_table_name(s) LIMIT offset_numeric_value, limit_numeric_value ;
- column1, column2… / * – specifies the fields’ list to be part of the output in select query.
- your_table_name(s) – specifies the name of your table from where the selection is made. There can be more than one table.
- offset_numeric_value – specifies the offset of the first row to return. It should be a numeric value.
- limit_numeric_value – specifies the maximum number of rows to be returned from your result set. It should be a numeric value.
Example:-
Let us assume that we want to return rows ranging from 10 to 20, including rows 10 and 20. Observe the below query for the solution.
SELECT * FROM customer_name_city LIMIT 9 , 11;
Action Message Output :-
11 row(s) returned.
Output:-

The output in figure 2 shows that rows ranging from 10 to 20 are selected.
KNOW MORE ABOUT LIMIT WITH OFFSET
MySQL select rows by range using ROW_NUMBER() function
The ROW_NUMBER() is a window function in MySQL that assigns a sequential number to each row. Hence we can use the row_number() function to select rows in a particular range. Let us look into the syntax followed by an example.
Syntax:-
SELECT column1, column2... / * ,ROW_NUMBER() OVER( ORDER BY <order_specification>) aias_row_num FROM your_table_name
- column1, column2… / * – specifies the list of fields to be part of the output in select query.
- alias_row_num – is the alias name given to the row numbers assigned to each row.
- your_table_name(s) – specifies the name of your table from where the selection is made. There can be more than one table.
- order_specification – specifies that the order by clause decides the arrangement for the sequence given to each row. <expression > [ASC|DESC] , [,{<expression>…}]
Example:-
Again we will select rows ranging from 10 to 20, where rows 10 and 20 are included. Observe the below query for the solution.
SELECT * FROM ( SELECT customer_id, customer_name, customer_city, ROW_NUMBER() OVER( ORDER BY customer_id ASC) row_num from customer_name_city ) cnc_alias WHERE cnc_alias.row_num BETWEEN 10 AND 20;
Action Message Output :-
11 row(s) returned.
Output:-

The output in figure 3 shows the rows between 10 to 20 in the result set. The output is the same as in figure 2 except for an additional column row_num.
Explanation:-
- Here in the subquery we are also adding row_num as part of the output.
- row_num is an alias column name for the sequential values generated by row_number() function, and the order is as per customer_id ascending.
- cnc_alias is the alias name given to subquery.
- All the columns from the inner subquery are selected.
- Finally, the result is filtered to get rows with row_num ranging from 10 to 20 using the BETWEEN keyword in the outer query.
We hope this article helped you with queries related to selecting rows within a range. Good Luck !!!