Ranking Functions

Rank(): Returns the rank of each row within the partition of the result set. The rank of each row is 1 + the ranks that came before the row in question.


Dense Rank(): Returns the rank of each row within the partition of a result set, without any gaps.  The rank of each row is 1 + the number of distinct ranks that came before it.

NTile(): Distributes the rows in an ordered partition into a specified number of groups. Groups are numbered starting at 1.

RowNumber(): Returns the sequential number of a row within the partition of a result set, starting at 1 for the first row in each partition.

I will give you the following example from http://blog.sqlauthority.com and BOL(Books online) and I am assuming you have MS SQL Server and adventureworks database :



USE AdventureWorks;GOSELECT c.FirstName, c.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.PostalCodeFROM Sales.SalesPerson sINNER JOIN Person.Contact cON s.SalesPersonID = c.ContactIDINNER JOIN Person.Address aON a.AddressID = c.ContactIDWHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;