/*Rank , RowNumber, Danse rank*/
Rank Function - It is used to get the rank of records on the basis of any column. It skips the records if matched the same.
DENSE_RANK Function - It is used to get the rank of records on the basis of any column. It doesn't skips the records if matched the same.
ROW_NUMBER Function - It is used to get the order of records on the basis
of any column. It reset itself once the shorted column values gets changed.
CREATE TABLE Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
(
id INT,
name VARCHAR(50) NOT NULL,
company VARCHAR(50) NOT NULL,
power INT NOT NULL
)
INSERT INTO Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
VALUES
(1, 'Corrolla', 'Toyota', 1800),
(2, 'City', 'Honda', 1500),
(3, 'C200', 'Mercedez', 2000),
(4, 'Vitz', 'Toyota', 1300),
(5, 'Baleno', 'Suzuki', 1500),
(6, 'C500', 'Mercedez', 5000),
(7, '800', 'BMW', 8000),
(8, 'Mustang', 'Ford', 5000),
(9, '208', 'Peugeot', 5400),
(10, 'Prius', 'Toyota', 3200),
(11, 'Atlas', 'Volkswagen', 5000),
(12, '110', 'Bugatti', 8000),
(13, 'Landcruiser', 'Toyota', 3000),
(14, 'Civic', 'Honda', 1800),
(15, 'Accord', 'Honda', 2000)
RANK Function
The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause.
SELECT name,company, power,
RANK() OVER(ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
Output
name company power PowerRank
800 BMW 8000 1
110 Bugatti 8000 1
208 Peugeot 5400 3
Atlas Volkswagen 5000 4
Mustang Ford 5000 4
C500 Mercedez 5000 4
Prius Toyota 3200 7
Landcruiser Toyota 3000 8
Accord Honda 2000 9
C200 Mercedez 2000 9
Corrolla Toyota 1800 11
Civic Honda 1800 11
City Honda 1500 13
Baleno Suzuki 1500 13
Vitz Toyota 1300 15
SELECT name,company, power,
RANK() OVER(PARTITION BY company ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
name company power PowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800 BMW 8000 1
110 Bugatti 8000 1
Mustang Ford 5000 1
Accord Honda 2000 1
Civic Honda 1800 2
City Honda 1500 3
C500 Mercedez 5000 1
C200 Mercedez 2000 2
208 Peugeot 5400 1
Baleno Suzuki 1500 1
Prius Toyota 3200 1
Landcruiser Toyota 3000 2
Corrolla Toyota 1800 3
Vitz Toyota 1300 4
Atlas Volkswagen 5000 1
DENSE_RANK Function
The DENSE_RANK function is similar to RANK function however the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records. Take a look at the following script.
SELECT name,company, power,
dense_RANK() OVER(ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
name company power PowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800 BMW 8000 1
110 Bugatti 8000 1
208 Peugeot 5400 2
Atlas Volkswagen 5000 3
Mustang Ford 5000 3
C500 Mercedez 5000 3
Prius Toyota 3200 4
Landcruiser Toyota 3000 5
Accord Honda 2000 6
C200 Mercedez 2000 6
Corrolla Toyota 1800 7
Civic Honda 1800 7
City Honda 1500 8
Baleno Suzuki 1500 8
Vitz Toyota 1300 9
SELECT name,company, power,
DENSE_RANK() OVER(PARTITION BY company ORDER BY power DESC) AS DensePowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
name company power DensePowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800 BMW 8000 1
110 Bugatti 8000 1
Mustang Ford 5000 1
Accord Honda 2000 1
Civic Honda 1800 2
City Honda 1500 3
C500 Mercedez 5000 1
C200 Mercedez 2000 2
208 Peugeot 5400 1
Baleno Suzuki 1500 1
Prius Toyota 3200 1
Landcruiser Toyota 3000 2
Corrolla Toyota 1800 3
Vitz Toyota 1300 4
Atlas Volkswagen 5000 1
ROW_NUMBER Function
Unlike the RANK and DENSE_RANK functions, the ROW_NUMBER function simply returns the row number of the sorted records starting with 1
SELECT name,company, power,
ROW_NUMBER() OVER(ORDER BY power DESC) AS RowRank
FROM Cars
name company power RowRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800 BMW 8000 1
110 Bugatti 8000 2
208 Peugeot 5400 3
Atlas Volkswagen 5000 4
Mustang Ford 5000 5
C500 Mercedez 5000 6
Prius Toyota 3200 7
Landcruiser Toyota 3000 8
Accord Honda 2000 9
C200 Mercedez 2000 10
Corrolla Toyota 1800 11
Civic Honda 1800 12
City Honda 1500 13
Baleno Suzuki 1500 14
Vitz Toyota 1300 15
SELECT name, company, power,
ROW_NUMBER() OVER(PARTITION BY company ORDER BY power DESC) AS RowRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
name company power RowRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800 BMW 8000 1
110 Bugatti 8000 1
Mustang Ford 5000 1
Accord Honda 2000 1
Civic Honda 1800 2
City Honda 1500 3
C500 Mercedez 5000 1
C200 Mercedez 2000 2
208 Peugeot 5400 1
Baleno Suzuki 1500 1
Prius Toyota 3200 1
Landcruiser Toyota 3000 2
Corrolla Toyota 1800 3
Vitz Toyota 1300 4
Atlas Volkswagen 5000 1
Similarities between RANK, DENSE_RANK, and ROW_NUMBER Functions
The RANK, DENSE_RANK and ROW_NUMBER Functions have the following similarities:
1- All of them require an order by clause.
2- All of them return an increasing integer with a base value of 1.
3- When combined with a PARTITION BY clause, all of these functions reset the returned integer value to 1 as we have seen.
4- If there are no duplicated values in the column used by the ORDER BY clause, these functions return the same output.
