Search⌘ K
AI Features

Answer: Using RANK()

Explore how to apply the RANK() window function in SQL to rank sales categories based on total sales. Understand the query structure, including SELECT statements, joins, GROUP BY, and ORDER BY clauses. Learn alternative approaches using CTEs, user-defined variables, and other ranking functions like DENSE_RANK() and ROW_NUMBER() to tackle ranking problems in SQL effectively.

Solution

The solution is given below:

MySQL
-- The query to rank the records using RANK()
SELECT PC.CategoryName,
SUM(S.SalesAmount) AS TotalSales,
RANK() OVER (ORDER BY SUM(S.SalesAmount) DESC) AS 'Rank'
FROM Sales AS S
JOIN ProductCategories AS PC ON S.CategoryID = PC.CategoryID
GROUP BY PC.CategoryName
ORDER BY TotalSales DESC;

Code explanation

The explanation of the solution code is given below:

  • Lines 2–4: The SELECT statement selects the columns CategoryName and TotalSales. The RANK() function assigns a rank to each category based on TotalSales in descending order. We use AS to set an alias for the calculated column.

  • Line 5: The FROM clause specifies the table name as Sales.

  • Line 6: The JOIN is applied to the ProductCategories table on the CategoryID column.

  • Line 7: The GROUP BY clause groups the results by CategoryName.

  • Line 8: The ORDER BY clause sorts the results by the TotalSales column in descending order.

Recalling relevant concepts

We have covered the following ...