MySQL select rows by range

This article will be looking into LIMITS with OFFSET, including the syntax, using examples.

Table of Contents

1. MySQL select rows by range using LIMIT with OFFSET
2. 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.

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