This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am comparing and sharing maths on ROW_NUMBER(), RANK(), and DENSE_RANK() of SQL Server. We are using these functions in day to day work, but still few people are not aware of it.
Something like, Can we use DENSE_RANK() as ROW_NUMBER()?
The answer is yes, we can use for DISTINCT records because using ROW_NUMBER(), you cannot generate distinct records as it applicable for all records.
Check the below samples, and try it yourself:
Create a table with sample records:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_numbers (name varchar(10)) GO INSERT INTO tbl_numbers VALUES ('ABC'),('ABC'),('ABC') ,('XYZ'),('XYZ'),('LMN'),('OPQ'),('OPQ') GO |
Compare the output of Row_Number, Rank, DENSE_RANK:
|
1 2 3 4 5 6 |
SELECT name ,ROW_NUMBER() OVER(ORDER BY name) AS RowNumber ,RANK() OVER(ORDER BY name) AS RankNumber ,DENSE_RANK() OVER(ORDER BY name) AS DENSE_RankNumber FROM tbl_numbers |
Result:
|
1 2 3 4 5 6 7 8 9 10 |
name RowNumber RankNumber DENSE_RankNumber ---------- -------------------- -------------------- -------------------- ABC 1 1 1 ABC 2 1 1 ABC 3 1 1 LMN 4 4 2 OPQ 5 5 3 OPQ 6 5 3 XYZ 7 7 4 XYZ 8 7 4 |
Using ROW_NUMBER, you cannot get the distinct records:
|
1 2 3 4 |
SELECT DISTINCT name ,ROW_NUMBER() OVER(ORDER BY name) AS RowNumber FROM tbl_numbers |
Result:
|
1 2 3 4 5 6 7 8 9 10 |
name RowNumber ---------- ------------ ABC 1 ABC 2 ABC 3 LMN 4 OPQ 5 OPQ 6 XYZ 7 XYZ 8 |
Using CTE, get the distinct records:
|
1 2 3 4 5 6 7 8 9 10 |
WITH CTE AS ( SELECT DISTINCT name FROM tbl_numbers ) SELECT name ,ROW_NUMBER() OVER(ORDER BY name) AS RowNumber FROM CTE |
Result:
|
1 2 3 4 5 6 |
name RowNumber ---------- ----------- ABC 1 LMN 2 OPQ 3 XYZ 4 |
Using DENSE_RANK, get the distinct records:
This query is faster than, CTE and you can use DENSE_RANK as a ROW_NUMBER for distinct Records.
|
1 2 3 4 |
SELECT DISTINCT name ,DENSE_RANK() OVER(ORDER BY name) AS DENSE_RankNumber FROM tbl_numbers |
Result:
|
1 2 3 4 5 6 |
name DENSE_RankNumber ---------- -------------------- ABC 1 LMN 2 OPQ 3 XYZ 4 |
Leave a Reply