SQL ROW_NUMBER

Introduction to SQL ROW_NUMBER

ROW_NUMBER in SQL is a window function that is used to assign each row in the result set a unique integer sequence number, starting from 1. It is as simple as giving each row a unique number like roll call to identify it. Since ROW_NUMBER is a windows function, it does not make any changes in the original data table.

It is very helpful when we want to find answers to questions such as what is the nth highest/ lowest entry in the resultset. ROW_NUMBER function can be used for performing pagination of the result set, instead of showing all the details at once. There are many uses of this function like the RANK() function in SQL. We will learn about most of them in this post using some examples.

Syntax and Parameters:

The basic syntax for writing ROW_NUMBER function in SQL is as follows :

ROW_NUMBER() OVER(
[PARTITION BY partition_expression] [ORDER BY order_expression [ASC | DESC]] )

The parameters used in the above syntax are as follows :

• partition_expression: The column or expression on the basis of which the entire dataset has to be divided. If you do not specify anything, by default the entire result set is considered as a single-window or partition.
• order_expression: The column or expression on the basis of which the rows in the partition set is ordered or sorted in a particular ascending or descending order.

Examples of SQL ROW_NUMBER

In order to illustrate ROW_NUMBER() function in great detail, let us create a table called “yearly_sales”.It contains details pertaining to sales made by a salesperson in a particular year. We can use the following code snippet to create the table.

CREATE TABLE public.yearly_sales
(
year smallint NOT NULL,
salesperson character varying(255) COLLATE pg_catalog."default" NOT NULL,
store_state character varying(255) COLLATE pg_catalog."default" NOT NULL,
sale_amount numeric NOT NULL
);

Having created the table, let us insert some records in it to work with. We can use the following code snippet to insert values.

INSERT INTO public.yearly_sales(
year, salesperson, store_state, sale_amount)
VALUES (2020,'Radhika Singh','DL',18000),
(2019,' Kate Dave','DL',12000),
(2020,'Kate Dave','DL',13260),
(2019,'Radhika Singh','DL',11200),
(2018,'Radhika Singh','KA',18000),
(2019,'Kate Dave','MH',14300),
(2018,'Kate Dave','MH',15100),
(2020,'Greg Morocco','NY',17200),
(2019,'Greg Morocco','NY',12350);

After the above-mentioned insertion operations, the data in the “yearly_sales” table looks something as shown below :

SELECT * FROM yearly_sales;

Now we are all set to try a few examples based on the newly created “yearly_sales” table.

Example #1: SQL query to illustrate the use of ROW_NUMBER() function to assign a sequential number to each row in the result set.

SELECT
year,
salesperson,
sale_amount,
store_state,
ROW_NUMBER () OVER (ORDER BY year)
FROM
yearly_sales;

In this example, since we have not created any partition, the entire result set by default is considered as a single partition. We can see in the data output that row_number() function has sequentially assigned a unique integer number to each row in the partition starting from 1 and ending at 9.

Example #2: Use of ROW_NUMBER() function to assign a row number to each row in a partition created by year in the result set.

SELECT
year,
salesperson,
sale_amount,
store_state,
ROW_NUMBER ()
OVER (PARTITION BY year ORDER BY sale_amount DESC)
FROM
yearly_sales;

We can observe in the image that the ROW_NUMBER() function first created partitions by year (2018,2019 and 2020) and then uniquely numbered each row starting from 1 within each partition.

Example #3: Use of ROW_NUMBER() function to find the name of the top salesperson
(the salesperson who made sales of the maximum amount) during the years 2018, 2019, and 2020.

WITH CTE AS (
SELECT
year,
salesperson,
sale_amount,
store_state,
ROW_NUMBER ()
OVER (PARTITION BY year ORDER BY sale_amount DESC) as row_number
FROM
yearly_sales )
SELECT year, salesperson
FROM CTE
WHERE row_number = 1;

For using row_number in the WHERE clause of a SQL query, we have to first use the ROW_NUMBER() function in a subquery or a common expression table (CTE) because it is a windows function and it assigns row numbers after the WHERE clause filtering is done. In this example, we have created a CTE to illustrate the same. In the next example, we will use a subquery to further illustrate the same.

Example #4: SQL query to illustrate use of ROW_NUMBER() function to perform pagination of a huge result set.

SELECT *
FROM
(SELECT
year,
salesperson,
sale_amount,
store_state,
ROW_NUMBER ()
OVER (ORDER BY year DESC) as row_number
FROM
yearly_sales ) t
WHERE row_number BETWEEN 4 AND 8
ORDER BY sale_amount;

Many times, we might have to create dashboards or web applications where we cannot show the entire result set on a single page. In such cases, we are required to perform pagination. This can be achieved by using ROW_NUMBER() functions as shown in the example above.

Conclusion

ROW_NUMBER () is a windows function that is used to sequentially number the rows or data records in a result set or a partition of it. It is similar to the RANK() function in some aspects. It is helpful in performing pagination, finding nth highest/lowest entry in the dataset.

Recommended Articles

This is a guide to SQL ROW_NUMBER. Here we discuss Introduction, syntax, parameters, and Examples with code implementation. You can also go through our other related articles to learn more –

1. SQL DELETE
2. MySQL Export Database
3. SQL Compare String
4. SQL LAG()