Rank function in sql

Question

How to use rank function in sql.

Akkiraju Ivaturi · Answer

USE AdventureWorks ; GO SELECT 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.PostalCode FROM Sales.SalesPerson s INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID INNER JOIN Person.Address a ON a.AddressID = c.ContactID WHERE TerritoryID IS NOT NULL AND SalesYTD 0 ; Resultset:

Sukesh Marla · Answer

Check this link http://www.sukesh-marla.com/2010/08/using-ranking-functions.html C heck this is correct answer if it helped.

Mangesh barmate · Answer

Select Rank() over (order by columnname dec) as rank from tablename; Rank() function generates gap, but Dense_Rank() function not generate gap. Gap means if you have 2 common value lets say 500 ist value and 500 2nd value after 400 3rd value. then Rank() gives 1,1,3. But Dense_Rank() function gives 1,1,2. See below link.... http://www.c-sharpcorner.com/UploadFile/6897bc/ranking-functions/ Try this!!!

Rank function in sql

Answers (3)