Hello friends, When you want to apply rank to any column without storing it in table. You can use DENSE_RANK in SQL. I will explain how you can achieve it. We have already explained regarding RANK() ,NTILE()  and ROW_NUMBER().

DENSE_RANK returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in queue.

Syntax : DENSE_RANK ( ) OVER (PARTITION BY  column_name  ORDER BY column_name ASC/DESC )

“PARTITION BY” divides the result set into partitions to which DENSE_RANK function is applied.

“ORDER BY” defines the order in which DENSE_RANK function is applied in a partitions applied above if any.

By using only ORDER BY you can write query as follows:

SELECT 
DENSE_RANK() OVER ( ORDER BY s.country) AS 'Dense Rank',
s.country,s.FirstName, s.LastName
,s.product,s.price from SALES s

Have a look at the below result set. I have used DENSE_RANK on the sales table with “ORDER BY” clause on the country column. The final out put will divide the result in 4 groups. Below screen shot demonstrate the DENSE_RANK in details.

But if you want to apply rank within Country column, then use PARTITION BY.

SELECT 
DENSE_RANK() OVER ( PARTITION BY s.COUNTRY ORDER BY s.FIRSTNAME) AS 'Rank',
s.country,s.FirstName, s.LastName
,s.product,s.price from #SALES s

Run in fiddle and see what you will get. 🙂

You can also visit my sqlfiddle at : http://sqlfiddle.com/#!3/fe5d8/11

Don’t forget to share your views.
Thanks 🙂