When it comes to working with structured data in the context of SQL and MySQL, it's essential to understand how to rank items. You might need to list items based on sales figures, popularity, or any other rating system. In this article, we'll take a look at how to accomplish this using the SQL RANK() function, along with practical examples.
The RANK() function assigns a unique rank to each row within a partition of a result set. Rows with the same values receive the same rank. In the following examples, we'll use the Northwind database to demonstrate practical use cases.
Ranking Products by Quantity Sold
In many scenarios, you might want to rank products based on their sales. Let's start by retrieving the top 10 products by quantity sold:
The following query will give you the desired result, ranking products based on their total quantity sold and displaying the top 10:
Using RANK() Function
The RANK() function picks up where the previous row's rank left off. If two rows have the same value, they will both receive the same rank, and the next rank will be skipped. Here's how to use it:
SELECT ProductName, SUM(Quantity) AS TotalQuantity, RANK() OVER (ORDER BY SUM(Quantity) DESC) AS RankPosition
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY ProductName
ORDER BY TotalQuantity DESC, ProductName ASC;
Alternative: Using DENSE_RANK() Function
If you want to avoid skipping ranks when two products have the same quantity, use the DENSE_RANK() function instead. Consecutive ranks are assigned even if the values are equal:
SELECT ProductName, SUM(Quantity) AS TotalQuantity, DENSE_RANK() OVER (ORDER BY SUM(Quantity) DESC) AS RankPosition
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY ProductName
ORDER BY TotalQuantity DESC, ProductName ASC;
Ranking Categories by Total Sales Value
Now, let's rank categories based on the total sales value of their products. We'll also consider only the top 5 categories:
Including Category Information
To include category information, we'll join the Categories table with the previous query. Here's how to achieve this:
SELECT C.CategoryName, SUM(OD.Quantity * P.UnitPrice) AS TotalSales, DENSE_RANK() OVER (ORDER BY SUM(OD.Quantity * P.UnitPrice) DESC) AS CategoryRank
FROM Orders O
INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID
INNER JOIN Products P ON OD.ProductID = P.ProductID
INNER JOIN Categories C ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName
ORDER BY TotalSales DESC, CategoryName ASC;
Ranking data is a powerful technique in SQL, enabling you to gain valuable insights from your data. Keep practicing and exploring different use cases to master this essential skill. Happy coding!