Ranking-Query

While working with sql queries , sometimes we need to sort result set and find out the top 10 products , prices etc . we use order by clause to sort the results either ascending or descending.
But what if someone told u to find out the top 10 records and display their ranks too besides.
Here is one demonstration of ranking query . SQL Server provides a special function rank() over that can solve the problem very easily and its is very useful .

Syntax : RANK () OVER ( < order_by_clause > )

For example if in sample DB “Adventure work” we want to find top 10 products according to unit price then we can use the query as

Select Distinct ProductID, UnitPrice , LineTotal, ReceivedQty,RANK() OVER (order by UnitPrice) as [Unit Price Rank]
from Purchasing.PurchaseOrderDetail
Where ProductID between 800 and 900
order by [Unit Price Rank]

It will return all products sorted alongwith their rank like