Skip to content

SQL RANK and DENSE_RANK

The functions RANK and DENSE_RANK are very similar to the ROW_NUMBER function. The difference is the ORDER_BY statement and the connections between these functions. The RANK function returns the same numbers as the repeating lines and the unused numbers are passed. In the DENSE_RANK function, unused numbers are not passed. We can describe this in a good example:

SELECT cus.first_name,cus.last_name,
RANK() OVER(ORDER BY cus.last_name) as [rank],
ROW_NUMBER() OVER(ORDER BY cus.last_name) as [row_number],
DENSE_RANK() OVER(ORDER BY cus.last_name) as [dense_rank]
FROM BikeStores.sales.customers as cus
GROUP BY cus.first_name,cus.last_name
ORDER BY cus.last_name asc
SQL RANK and DENSE_RANK
SQL RANK and DENSE_RANK

rank: This function will assign a unique number to each distinct row, but it leaves a gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().

SELECT cus.first_name, cus.last_name,
RANK() OVER(PARTITION BY cus.last_name ORDER BY cus.first_name) as family_rank
FROM BikeStores.sales.customers as cus;
SQL RANK and DENSE_RANK

dense_rank: This function is similar to Rank with only difference, this will not leave gaps between groups.

SELECT cus.first_name, cus.last_name,
DENSE_RANK() OVER(PARTITION BY cus.last_name ORDER BY cus.first_name) as family_rank
FROM BikeStores.sales.customers as cus;
SQL RANK and DENSE_RANK
SQL RANK and DENSE_RANK

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: