MS SQL Server

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.

  1. 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.
  2. 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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list