Ranking functions in sql server with examples

CREATE TABLE #TABLE3

(

       ProductId INT IDENTITY,

       ProductName VARCHAR(50),

       Quantity INT,

       Price MONEY,

       RefDate DATETIME

)

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('A',10,100,'2016/01/01')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('A',20,200,'2016/01/02')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('A',30,300,'2016/01/03')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('B',40,200,'2016/01/04')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('B',50,500,'2016/01/05')

INSERT INTO #TABLE3(ProductName,Quantity,Price,RefDate)VALUES('C',60,300,'2016/01/06')

SELECT * FROM #TABLE3

ProductId   ProductName   Quantity   Price    RefDate

---------   -----------   --------   ------   -----------------------

1           A             10         100.00   2016-01-01 00:00:00.000

2           A             20         200.00   2016-01-02 00:00:00.000

3           A             30         300.00   2016-01-03 00:00:00.000

4           B             40         200.00   2016-01-04 00:00:00.000

5           B             50         500.00   2016-01-05 00:00:00.000

6           C             60         300.00   2016-01-06 00:00:00.000

(6 row(s) affected)

Question

Describe below ranking function?

1. ROW_NUMBER ()

2. RANK ()

3. DENSE_RANK ()

Answer

1. ROW_NUMBER ()

The ROW_NUMBER () function returns the sequential numbers, starting at 1, for the rows in a result set based on column. The ORDER BY keyword in the OVER clause specifies that the result set will appear in the descending order of the ProductId column.

SELECT ProductId,Price,ROW_NUMBER() OVER(ORDER BY Price DESC)'Rank'

FROM #TABLE3

ProductId   Price    Rank

---------   ------   ----

5           500.00   1

6           300.00   2

3           300.00   3

4           200.00   4

2           200.00   5

1           100.00   6

(6 row(s) affected)

2. RANK ()

The RANK () function returns the rank of each row in a result set based on specified criteria.

For example, you want to rank the products based on the purchase made during a year. For this, you can use the RANK function. This function will consider the ORDER BY clause and the record with maximum value will get the highest rank if the ORDER BY clause is ASC. 

Consider the following example, you need to create the report of all the product with their price. The highest ranked product should be given the rank as 1. In addition, if two products have the same price, they should be given the same rank. However, the next rank number will be addition of the rank of the products that have the same price and the number of products holding the same price.  

SELECT ProductId,Price,RANK() OVER(ORDER BY Price DESC)'Rank'

FROM #TABLE3

ProductId   Price    Rank

---------   ------   ----

5           500.00   1

6           300.00   2

3           300.00   2

4           200.00   4

2           200.00   4

1           100.00   6

(6 row(s) affected)

3. DENSE_RANK ()

The function is used where sequential ranking values need to be given based on a specified criteria. It performs the same ranking task as the RANK function, but provides sequential ranking values to an output. For example, you want to rank the products based on the purchase made during a year. If two products have the same price, they should be given the same rank. However, the next rank number would be assigned the next rank value.  

Consider the following example, you need to give the same rank to the products with the same price and sequential rank to the next one.

SELECT ProductId,Price,DENSE_RANK() OVER(ORDER BY Price DESC)'Rank'

FROM #TABLE3

ProductId   Price    Rank

---------   ------   ----

5           500.00   1

6           300.00   2

3           300.00   2

4           200.00   3

2           200.00   3

1           100.00   4

(6 row(s) affected)