Rank function in sql
How to use rank function in sql.
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Akkiraju IvaturiPosted Sep 7, 2012, 6:42 PM
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 MarlaPosted Sep 7, 2012, 1:14 PM
http://www.sukesh-marla.com/2010/08/using-ranking-functions.html
Check this is correct answer if it helped.
Mangesh barmatePosted Sep 7, 2012, 12:19 PM
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!!!