ROW_NUMBER() in MySQL

The ROW_NUMBER() is a window function in MySQL that assigns a sequential number to each row. In this artifact, we will discuss the syntax and different examples around the row_number() function to clarify the concept.

Table of Contents

1. ROW_NUMBER() in MySQL Syntax
2. ROW_NUMBER() in MySQL Examples
3. ROW_NUMBER() in MySQL Example with Partition
4. Using ROW_NUMBER() in MySQL with Example

Let us get started by making the data that is to be used across. We will be creating a table customer_details and inserting data into it.

CREATE TABLE customer_details (
    customer_id INT AUTO_INCREMENT,
    customer_name VARCHAR(255),
    customer_address VARCHAR(255),
    primary key (customer_id));
INSERT INTO customer_details (customer_name,customer_address) 
VALUES("Gustav","56A Denmark"),
("Henric","255 USA"),
("Richa","78 India"),
("Margit","58 Canada"),	
("Henric","18 Libya"),
("Henric","43 Sweden"),
("Richa","78 Singapore"),
("Henric","255 Italy"),
("Rohit","899 Bangladesh");

Let us see what got into the table customer_details by executing:

SELECT * FROM customer_details;

Output:-

ROW_NUMBER() in MySQL Syntax

Let us look into the syntax for ROW_NUMBER() function.

ROW_NUMBER() OVER (<partition_specification> <order_specification>)

Syntax for <partition_specification>

PARTITION BY clause is optional.

PARTITION BY <expression> ,[{,<expression> } ... ]

Syntax for <order_specification>

ORDER BY clause is mandatory. ASC is the default value.

ORDER BY <expression> [ASC|DESC],[,{<expression>}...]

The row_number() function assigns a designated number to the current row corresponding to the partition.

Row numbers assigned to each row range between 1 to the number of partition rows.

The order by clause decides the arrangement for the sequence given to each row. Hence, in case the order is changed, the sequential number assigned to each row changes.

ROW_NUMBER() in MySQL Examples

Let us forge ahead by looking into a basic example to assign row numbers to the table customer_details. Observe the below query and its output for the solution.

 SELECT 
  *,
  ROW_NUMBER() OVER(ORDER BY customer_id ASC) AS sequence_number
  FROM customer_details;

Action Message Output Response:-

9 row(s) returned.

Figure 2 shows an extra column in the result that is a sequential number—the number assigned to each row based on the ascending order of the customer_id. Let us change the order to descending and view the output.

  SELECT 
  *,
  ROW_NUMBER() OVER(ORDER BY customer_id DESC) AS sequence_number
  FROM customer_details;

Action Message Output Response:-

9 row(s) returned.

Output:-

We will take one more example by changing the order again based on customer_name. Observe the difference in the output.

  SELECT 
  *,
  ROW_NUMBER() OVER(ORDER BY customer_name) AS sequence_number
  FROM customer_details;

Action Message Output Response:-

9 row(s) returned.

Output:-

Figure 4 shows that sequencing is done based on customer_name.

ROW_NUMBER() in MySQL Example with Partition

Let us see how to use the ROW_NUMBER() function with PARTITION. We will be using the table customer_details and forming GROUPS based on customer_name. Observe the below query.

  SELECT 
  *,
  ROW_NUMBER() OVER(PARTITION BY customer_name ORDER BY customer_id) AS row_num
  FROM customer_details;

Action Message Output Response:-

9 row(s) returned.

Output:-

Figure 5 shows that smaller partitions have been formed based on customer_name, and row numbers have been assigned within these partitions starting from 1. Order is customer_id ascending.

Using ROW_NUMBER() in MySQL with Example

In this section, we will look into how to use the row_number() function in MySQL. It is generally used when we want to select a row corresponding to its sequence number or if we want to limit our result set in the output.

We will be looking into two examples to demonstrate its usage:

Row_Number() usage Example 1 :

Get the latest customer_id inserted for customer_name =”Henric”

Observe the below query for the solution.

 SELECT 
  cd.customer_id,
  cd.customer_name, 
  cd.customer_address
  FROM ( SELECT 
  customer_id, customer_name, customer_address,
  ROW_NUMBER() OVER(PARTITION BY customer_name ORDER BY customer_id DESC) row_num
  FROM customer_details
  ) cd 
  WHERE cd.row_num =1 and customer_name = "Henric";

Action Message Output Response:-

1 row(s) returned.

Output:-

Here we have partitioned the table based on customer_name and ordered by descending customer_id. Hence the row with customer_id ‘8’ is assigned with row number 1. Finally, the query filters the result to get rows with row_num = 1 and customer_name = “Henric.”

Row_Number() usage Example 2 :

Get first five records from table customer_details ordered by customer_name.

Observe the below query for the solution.

SELECT *
FROM 
    (SELECT customer_id, customer_name ,
customer_address,
         row_number()
        OVER (order by customer_name) AS row_num
    FROM customer_details) t
WHERE row_num BETWEEN 1 AND 5;   

Output:-

Here we are getting the first 5 records from the customer_details table when ordered by customer_name.

We hope this tutorial helps in understanding the concept of ROW_NUMBER() in MySQL. Good Luck !!!