Identifying Top-N Categories

In this lesson, you will learn how to isolate the best-performing segments of your data by combining aggregation with precise sorting.

The Logic of "Top-N"

When you perform a grouped analysis—such as calculating total sales per product category—you often end up with a long list of results. To focus on your "winners," you need to extract only the top N rows (e.g., the "Top 5" or "Top 10").

To achieve this, we follow a specific sequence:

1. Group: Use GROUP BY to bundle your data into categories.
2. Aggregate: Use functions like SUM() or COUNT() to create a metric for each group.
3. Sort: Use ORDER BY to place the highest values at the top of the result set.
4. Limit: Use the LIMIT clause to truncate the list to your desired number of rows.

Practical Example

Imagine you are running an online store and want to find your Top 3 product categories based on the total quantity sold.

SELECT 
    category_name, 
    SUM(quantity_sold) AS total_volume
FROM sales
GROUP BY category_name
ORDER BY total_volume DESC
LIMIT 3;

Why the Order Matters

Recall our lesson on the Execution Order of Clauses. The database must perform the GROUP BY and the calculation of total_volume before it can possibly know which rows should appear at the top. Because ORDER BY and LIMIT occur after the aggregation, they effectively act as a filter on your final, summarized report.

If you wanted to see the worst-performing categories instead, you would simply change DESC (descending) to ASC (ascending). This allows you to quickly shift your focus from top performers to under-performing segments without rewriting your entire query logic.

By limiting your output, you turn an overwhelming dataset into actionable business intelligence, allowing you to highlight the most significant trends within your database immediately.

Key Takeaway: To identify the top performers, always aggregate your data, sort the results in descending order, and apply a limit to truncate the list to your target number of entries.