(SQL SERVER) ROW_NUMBER (), RANK () AND DENSE_RANK ()

              

              

                Generating serial numbers is made easy by the use of in-built functions Row_number (), Rank () and Dense_Rank () in SQL SERVER. Let’s understand the use of these functions with a simple example.

Row_Number()

Row_number() is used to generate serial numbers in sequential order. It outputs the result by including the column names in the ORDER BY clause. Based upon the column, it will generate the result.

Let’s create an example table first and insert some dummy values into it,

CREATE TABLE Products

(

ProductId INT IDENTITY(1,1),

ProductName VARCHAR(50),

Price INT,

ProductRating INT

)

Go

INSERT INTO Products

VALUES ('Milk', 15, 5 ),

       ('Fresh Juice', 20, 4 ),

       ('Horlicks', 200, 2 ),

       ('Boost', 200, 3 ),

       ('Complan', 300, 3 )

GO

SELECT * FROM Products

When executing the select query produces the below output,

If we want to add serial numbers along with the output, the SQL statement to be used is,

SELECT ProductId,ProductName,Price,ProductRating,

ROW_NUMBER() OVER (ORDER BY ProductId) AS _Row_number FROM Products

The above SQL statement will produce an output,

It generates running serial numbers by including ProductId in the ORDER BY clause. Let’s say, if we want to generate serial numbers based upon the Price column then the output will be different. To understand it further execute the below code to view the output.

SELECT ProductId,ProductName,Price,ProductRating,

ROW_NUMBER() OVER (PARTITION BY Price ORDER BY ProductId) AS _Row_number FROM Products

As you can see, the serial numbers is changed to display according to  the price column.It is achieved by including the column name price in the PARTITION BY. The PARTITION BY acts similar to the GROUP BY to generate the serial numbers.

Rank()

Rank()  function is used to assign rank based on a particular column. For example, if there are two products that share the same price then the rank generated will be same let’s say 1, where in the third product with some other value will generate the rank 3 instead of 2. To understand it better execute the below SQL query and view the output.

SELECT ProductId,ProductName,Price,ProductRating,

RANK() OVER (ORDER BY Price) AS _Rank FROM Products

           

                As you can see, the products with price 200 generates the rank 3 but the next price generates 5 instead of 4.

Dense_Rank()

            Dense_Rank() function is similar to Rank() with one difference that if in rank() the number generated for two products sharing same value is 3, then the next product will have rank number 5, but Dense_Rank() generates the next number to the previous number. Lets understand this with executing the select query,

SELECT ProductId,ProductName,Price,ProductRating,

DENSE_RANK() OVER (ORDER BY Price) AS _Dense_Rank FROM Products

The above SQL statement produces an output,

The products with price 200 generates the rank 3 whereas the next product generates the next number 4. This function will be useful when trying to retrieve highest to lowest ranks as 1^st, 2^nd, 3^rd , etc. Accordingly, we can use PARTITION BY to filter the Rank() and Dense_Rank() further.