SQL - RANK, DENSE_RANK and ROW_NUMBER Functions

CREATE TABLE Cars

(

id INT,

name VARCHAR(50) NOT NULL,

company VARCHAR(50) NOT NULL,

power INT NOT NULL

)

INSERT INTO Cars

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)

---------------------

SELECT name,company, power,

RANK() OVER(ORDER BY power DESC) AS PowerRank

FROM Cars

SELECT name,company, power,

RANK() OVER(PARTITION BY company ORDER BY power DESC) AS PowerRank

FROM Cars

SELECT name,company, power,

DENSE_RANK() OVER(ORDER BY power DESC) AS DensePowerRank

FROM Cars

SELECT name,company, power,

DENSE_RANK() OVER(PARTITION BY company ORDER BY power DESC) AS DensePowerRank

FROM Cars

SELECT name,company, power,

ROW_NUMBER() OVER(ORDER BY power DESC) AS RowRank

FROM Cars

SELECT name, company, power,

ROW_NUMBER() OVER(PARTITION BY company ORDER BY power DESC) AS RowRank

FROM Cars

-------------------

--The only difference between RANK, DENSE_RANK and ROW_NUMBER function is when there are duplicate values in the column being used in ORDER BY Clause.

SELECT name, company, power,

RANK() OVER(PARTITION BY company ORDER BY power DESC) AS PowerRank,

DENSE_RANK() OVER(PARTITION BY company ORDER BY power DESC) AS DensePowerRank,

ROW_NUMBER() OVER(PARTITION BY company ORDER BY power DESC) AS RowRank

FROM Cars

SELECT name,company, power,

RANK() OVER(ORDER BY power DESC) AS [Rank],

DENSE_RANK() OVER(ORDER BY power DESC) AS [Dense Rank],

ROW_NUMBER() OVER(ORDER BY power DESC) AS [Row Number]

FROM Cars

SELECT name,company, power,

RANK() OVER(PARTITION BY company ORDER BY power DESC) AS PowerRank

FROM Cars

SQL - DENSE_RANK ()

DENSE_RANK ()

RANK: This gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. Therefore, if you have 3 items at rank 2, the next rank listed will be ranked 5.

DENSE_RANK: This gives you the ranking within your ordered partition, but the ranks are consecutive in it. Also, no ranks are skipped if there are ranks with multiple items.

This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

DENSE_RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )

CREATE TABLE sales.dense_rank_demo ( v VARCHAR(10) ); INSERT INTO sales.dense_rank_demo(v) VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');

SELECT * FROM sales.dense_rank_demo;

SELECT v, DENSE_RANK() OVER ( ORDER BY v ) my_dense_rank, RANK() OVER ( ORDER BY v ) my_rank FROM sales.dense_rank_demo;

Here is the output: