How to Use the SQL Server Row Number

In most cases, when we need to number items in a database, we jump to the identity property. However, what happens when you need to number the rows of a result? This is where the row number function comes into play.

The row number function allows you to assign a sequential number to every row as a result of an SQL Query.

The row_number() function is part of the SQL Server window functions. Using this function, you can assign a progressive integer to every row on each partition set of a result. Each number starts at 1and resets for the rows in each partition.

Function Syntax and Return Value

The function’s syntax is as shown:

ROW_NUMBER ()
OVER (partition BY partition_expression
ORDER BY order_by_expression
);

Let us break down the above syntax.

Partition by – The partition by clause allows you to divide your result set into various logical partitions. The row_number function is then applied to each partition. The partition by parameter is optional and, if not specified, the row_number function will treat the resulting set as a sole partition.
The order by clause allows you the sorting order for the rows within each partition set. Unlike the partition by clause, the row_number function requires this clause as an order-sensitive function.

The function returns by assigning a sequential number to the rows in each partition. As mentioned, the function will reset the row number for each new partition.

SQL Server Row_Number(): Examples

Let us use an example to understand better how to use the row_number() function. Start by creating a sample database with dummy data as shown in the queries below:

CREATE DATABASE dummy_db;
USE dummy_db;
CREATE TABLE dummy_table(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
firstName VARCHAR(50),
lastName VARCHAR(50),
email VARCHAR(100),
salary money,
department VARCHAR(50)
);
INSERT INTO dummy_table(firstName, lastName, email, salary, department)
VALUES ('Karen', 'Colmen', '[email protected]', $149000, 'Game Development'),
('Alex', 'Bell', '[email protected]', $150000, 'Graphics Development'),
('Charles', 'Johnson', '[email protected]', $120500, 'DevOps Development'),
('Bruce', 'Greer', '[email protected]', $118000, 'Security Development'),
('Sarah', 'Austin', '[email protected]', $165000, 'Game Development'),
('Diana', 'Kim', '[email protected]', $105000, 'Front-End Development'),
('Peter', 'Cogh', '[email protected]', $100000, 'Graphics Development'),
('David', 'Hugh', '[email protected]', $126000, 'Database Development'),
('Tobias', 'Newne', '[email protected]', $115500, 'Database Development'),
('Winnie', 'Lorentz', '[email protected]', $175000, 'Graphics Development'),
('Guy', 'Miche', '[email protected]', $145000, 'Game Development');

SELECT * FROM dummy_table;

The above query should return a resulting set as shown:

Example 1

The following SQL statement uses the row_number function to assign a sequential number to the rows in the resulting set:

SELECT ROW_NUMBER() OVER (
ORDER BY salary) AS row_num,
firstName,
lastName,
department
FROM dummy_table;

The above query should return a result set as shown below:

Example 2

We can use the row_number function to locate the employee with the highest salary in a specific department.

Consider the example query shown below:

SELECT firstName, lastName, salary, department, ROW_NUMBER() OVER (partition BY department ORDER BY salary DESC) AS row_num FROM dummy_table;

The query above divides the data into logical partitions based on the department. We then apply the row_number() function to order by the salary in descending order.

Example 3

You can use the row_number function for pagination. Since the row_number function assigns a sequential number to all the rows, we can use it to filter for a specific number of results per page.

Take the example below:

SELECT * FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY salary) AS row_num, firstName, lastName, department
FROM dummy_table) dt
WHERE row_num >= 1 AND row_num <= 5;

The above query should return an output as:

Conclusion

In this guide, we discussed how to use the SQL Server row_number() function to assign sequential numbers to the rows in a result set. Plus, we reviewed the function syntax and return value. We hope you found this article helpful. Check out more Linux Hint articles for tips and tutorials.