DENSE_RANK Function in SQL server ?

Dense Rank function returns the rank of each row within the partition without having any gaps in the ranking.

Syntax

DENSE_RANK () OVER ([PARTITION BY CLAUSE] ORDER BY CLAUSE)

Example

The following example get the dense rank value from the studentmarks table using the dense rank function and partition by clause.

The dense rank function will not skip the rank order even if the order by clause column having same value.

You can also use the dense rank function by removing the partition by clause as shown below.

In above example you can see the rank value of the student john is repeating two times since he scored the same marks in both subjects.In Case of dense rank the next rank value will not be skipped.

Rank function in SQL server ?

Rank function returns the rank of each row within the partition

Syntax

RANK () OVER ([PARTITION BY CLAUSE] ORDER BY CLAUSE)

Example

The following example get the rank value from the studentmarks table using the rank function and partition by clause.

You can also get the rank function by removing the partition by clause.

The rank function will skip the rank order if the order by clause column having same value.

In above example you can see the rank value of the student john is repeating two times since he scored the same marks in both subjects.So the rank 2 will be skipped in this case.

ROW_NUMBER function in SQL server ?

To return the Sequential number of row within the partition of a result set we can use the Row_Number function.

 For each partition the starting row number will be 1.

Its return type is BIGINT.

SYNTAX

ROW_NUMBER() OVER ( [PARTITION BY VALUE_EXPRESSION] ORDER BY CLAUSE)

EXAMPLE

In below example I have created Rownumber based on the UID column.

In below example I have created the department-wise Rownumber using Partition by clause.

Row_Number order will not exactly generated unless it satisfies the following condition.

• Values of ORDER BY columns are Unique
• Values of Partitioned column are Unique
• Values of both ORDER BY and Partitioned columns are Unique.