row_number
numbers the rows 1, 2, 3, etc by the columns in the ORDER BY clause, and if there are ties, it is arbitrary which rows that gets the same number.
rank
and dense_rank
are similar to row_number
, but when there are ties, they will give the same value to the tied values. rank
will keep the ranking, so the numbering may go 1, 2, 2, 4 etc, whereas dense_rank
will never give any gaps.
Study this example:
CREATE TABLE #data(MainID int NOT NULL,
Component varchar(10) NOT NULL,
Value decimal(12,2) NOT NULL,
PRIMARY KEY (MainID, Component)
)
go
INSERT #data (MainID, Component, Value)
VALUES(1, 'ALPHA', 12),
(1, 'BETA', 12),
(1, 'GAMMA', 13),
(1, 'DELTA', 12.23),
(1, 'EPSILON', 11.1),
(2, 'London', 9.12),
(2, 'New York', 8.11),
(2, 'Mumbai', 9.12)
go
SELECT MainID, Component, Value,
rowno = row_number() OVER(PARTITION BY MainID ORDER BY Value),
rank = rank() OVER(PARTITION BY MainID ORDER BY Value),
drank = dense_rank() OVER(PARTITION BY MainID ORDER BY Value)
FROM #data
ORDER BY MainID, Value
go
DROP TABLE #data
If there are no ties, the three functions will yield the same result.