MS SQL Server 8 - RANK and DENSE_RANK

Hello ALL,

Welcome back,

As usual! I am chakrapani Upadhyaya, a full stack engineer. In this article we will learn two functions that are helpful in any type of ranking problems. 

Note that these all are window functions

These functions are used to order the values and assign them numbers depending on where they fall in relation to one another. We use OVER() clause along with PARTITION BY and ORDER BY in both the functions.

PARTION BY :

The PARTITION BY clause divides the result set into partitions and changes how the window function is calculated. 

For Example :

Let’s say we have product with total items sold. If you partitioned the data by product, it would give you the ranking of each item sold, grouped by product.

ORDER BY:

ORDER BY specifies the column whose values you wish to rank. In our example item sold would be specified after ORDER BY.

Example:

Lets assume, we have three products from different stores 

• Store1 --> Product1 has 30 items sold, 
• Store1 --> Product2 has 25 items sold and
• Store2 --> Product3 has 56 items sold

With the help of these functions we can assign a 1, 2, or 3 to each product based on the total items sold you wish to rank them. Shown below

• Store1 --> Product3 ----> Rank 1
• Store2 --> Product1 ----> Rank 2
• Store3 --> Product2 ----> Rank 3

Sounds Good....

Lets look into this with real sql server example.

1. Lets begin with Creating Database and use the same

Create Database CodeWithChakri_DB

Use CodeWithChakri_DB

2. Then we will create the table and insert some records to it

--Create the table
Create Table ProductInfo
(
	ProductId	INT PRIMARY KEY IDENTITY(1,1),
	ProductName VARCHAR(200),
	StoreName   VARCHAR(200),
	ItemsSold	INT
)


--Insert records to the table
INSERT INTO ProductInfo VALUES('Samsung','Mobiles',96)
INSERT INTO ProductInfo VALUES('Apple','Mobiles',96)
INSERT INTO ProductInfo VALUES('Dell','Laptop',56)
INSERT INTO ProductInfo VALUES('Lenovo','Laptop',33)
INSERT INTO ProductInfo VALUES('MI','Laptop',33)

Great we have created the table and inserted the records to it, now its time to write a query to get the records.

Our main goal is to get the highest product sold by rank

Lets try Rank() Function first.

SELECT ProductName, RANK() OVER(ORDER BY ItemsSold ASC) AS Max_Sold 

FROM ProductInfo GROUP BY ProductName,ItemsSold

Here is the Output

Now we will write a query for DENSE_RANK() function

SELECT ProductName, DENSE_RANK() OVER(ORDER BY ItemsSold ASC) AS Max_Sold 

FROM ProductInfo GROUP BY ProductName,ItemsSold

Here is the output for DENSE_RANK() Function.

Please go through the both output carefully, definitely you will get the difference between RANK() and DENSE_RANK() functions.

Didn't get, Don't worry......

So with RANK, if the two Items are given a ranking of 1, the next lowest value would be assigned a rank of 3, skipping over 2. 

With DENSE_RANK, the next lowest value would be assigned a rank of 2, not skipping over any values.

Again, you can see there is no rank 2 in the column Max_Sold using RANK function in contrast to the DENSE_RANK function column which contains rank 2 and ends with rank 3 despite there being 4 rows in the table.

It depends on you, which one to use. But I would like to use DENSE_RANK as it provides data in order and does not skip any values.

I hope you enjoyed this article. That's it for right now, see you in next article until then take care bye bye 😊