Ranking Function In SQL Server

Ranking Functions

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are non deterministic.

Example :

USE AdventureWorks2012;

GO

SELECT 

p.FirstName, p.LastName

,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"

,RANK() OVER (ORDER BY a.PostalCode) AS Rank

,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"

,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile

,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson AS s

INNER JOIN Person.Person AS p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.Address AS a

ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL

AND SalesYTD <> 0;