Ranking functions are useful for providing ranking on the table columns based on the value stored in it. Say for example a table having students and their total marks in a separate column allows you to use the ranking function on the total marks column. With a ranking function you can easily find the second maximum or fourth maximum marks easily in SQL 2005 or in later versions.
The useful ranking functions are displayed below:
In this article, we look at how to use those ranking functions by using the NorthWind Sample database.
2. Demo View
First we will create a view from the NorthWnd database that will help with the explanation of the ranking functions. The following is the view:
Create View NetSales as
Select Ord.OrderId, Cat.CategoryName, Prod.ProductName,
Ord.UnitPrice * Ord.Quantity as NetSales
from (Categories cat INNER Join Products prod
ON cat.CategoryId = Prod.CategoryId)
INNER Join [Order Details] Ord
ON ord.ProductId = Prod.ProductId;
In the preceding view, we joined the category, products and Order Details tables together to get the required data. Also note that theNetSales is the total sales of a particular product for a given order id. We will perform ranking functions on this Netsales view. The result of the view is shown below. (Note that view does not have the order by clause as it is shown in the select query.)
" target="_blank">" style="margin:5px; width:534px">
3. ROW_NUMBER function
Row number applies to the sequence of numbers for each row. This is useful when you want to delete duplicate rows in a table. The following example shows how the row_number is applied on the ascending ordered netsales:
" target="_blank">" style="margin:5px; width:526px">
Here, Rows marked under A, B and C has the same NetSales values. And note that the Row Number is applied sequentially without repititions, even the NetSales values are the same.
4. RANK function
In the following example we used a RANK function to provide a Rank for the products based on the Net Sales it generated. Note that we applied a Rank on NetSales column based on the values it holds in a descending order. So the First Rank is given to the highest net sales and the 2nd Rank is given to Next Highest and so on; see:
" target="_blank">" style="margin:5px; width:554px">
In the preceding example, the set of rows marked in A, B and C have the same NetSales. Note that the same rank is given for the products, which holds the same netsales on a given order. Also note the Gaps in the Rank, say we do not have Rank 2, as there are two products in Rank 1.
The following example shows a different order for each of the Rank and Select Query's statements. Note that the ranking is still applied on the descending order of Netsales and the output is ordered based on the Product Name.
" target="_blank">" style="margin:5px; width:528px">
5. DENSE_RANK function
The DENSE_RANK function works the same as a rank function and the only difference is that it avoids the gaps in the rank. The following example shows the dense rank in effect:
" target="_blank">" style="margin:5px; width:587px">
Note that even though the Rank 1 is shared between two rows, the next rank given to the NetSales of 10540.00 is 2 not 3. And this is how dense rank differs from the normal rank function.
6. NTILE function
NTILE function is a normal distribution function. Say for example NTILE (100) means percentile of 100 and when we apply that on the Netsales in Descending orders, the sales is distributed in 100 groups. The top sales in value are placed on percentile 1 and the least sales in value is placed in percentile 100. Say for example if you call NTILE(5) in place of NTILE(100), then you are placing the Netsales in 5 groups stating TOP 5 meaning that RANK 1 group, Rank 2 Group, Rank3 Group, Rank 5 Groups.
In the following example the Netsales is distributed on a 100 percentile:
" target="_blank">" style="margin:5px; width:663px">
7. Applying Rank Functions within a Group
All the preceding examples used rank functions for the full table of data. But we can use the rank functions for a group and the rank will be reset when the group changes. In our example, we can apply a rank function for Netsales within each category. Doing so will rank the NetSales of product within each category. The following is the example for it:
" target="_blank">" style="margin:5px; width:623px">