Ranking function syntax are similar for all 4 different type of ranking functions.
- [ROW_NUMBER()|RANK() |DENSE_RANK ( )|NTILE()] OVER ( [ <partition_by_clause> ] < order_by_clause > )
Partition: use for group the result set.
- Row Number: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
- Rank: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
- DENSE_RANK: Similer to Rank but without the gap.
- NTILE:Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
ROW_NUMBER
- SELECT PSC.Name Category,p.Name Product,p.ListPrice,
- ROW_NUMBER() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice DESC) AS Row
- FROM Production.Product p
- JOIN Production.ProductSubCategory PSC
- ON p.ProductSubCategoryID=PSC.ProductSubCategoryID
- ORDER BY Category,ListPrice DESC
Because we partition by PSC.Name( Category) therefore you can see the result has been group as many subset group.
RANK
- SELECT Name ,ListPrice,
- RANK() OVER(ORDER BY ListPrice DESC) AS Rank
- FROM Production.Product
DENSE_RANK
- SELECT Name ,ListPrice,
- DENSE_RANK() OVER(ORDER BY ListPrice DESC) AS Rank
- FROM Production.Product
NTITLE
Below example divide the result set to 10 groups and then shows which group the row belong to.
- SELECT NTILE(10) OVER(PARTITION BY PC.Name ORDER BY P.ListPrice DESC) AS PriceBand,
- pc.Name Category,p.Name Produc,p.ListPrice
- FROM Production.Product p
- JOIN Production.ProductSubCategory PSC
- ON p.ProductSubCategoryID=PSC.ProductSubCategoryID
- JOIN Production.ProductCategory pc
- ON PSC.ProductCategoryID=pc.ProductCategoryID
- ORDER BY Category,ListPrice DESC
Rolex offers fake rolex UK a variety of models, from professional to classic watches, replica rolex datejust Watches to fit any wrist. Choose your favorite model, material, bezel, dial and bracelet, explore the Rolex series, and find the watch that suits you.
ReplyDelete