How to use Common Table Expression (CTE) In MySQL
Utilizing a Common Table Expression enhances readability and provides a clear structure for the query.
Syntax:
WITH ranked_data AS (
SELECT
your_columns,
ROW_NUMBER() OVER (PARTITION BY your_grouping_column ORDER BY your_ordering_column DESC) AS row_num
FROM
your_table
)
SELECT *
FROM ranked_data
WHERE row_num <= N;
Example
WITH ranked_data AS (
SELECT
product,
category,
revenue,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS row_num
FROM
sales
)
SELECT *
FROM ranked_data
WHERE row_num <= 2;
Output:
| product | category | revenue | row_num |
|------------------|-------------|---------|---------|
| Smartphone | Electronics | 1200 | 1 |
| Laptop | Electronics | 1000 | 2 |
| Washing Machine | Appliances | 900 | 1 |
| Refrigerator | Appliances | 800 | 2 |
| Coffee Maker | Appliances | 600 | 3 |
How to Restrict Results to Top N Rows per Group in MySQL
When working with MySQL databases, the need to limit results to the top N rows per group is a common requirement. This is particularly useful in scenarios where you aim to retrieve a specific number of records for each distinct group in your dataset. Achieving this involves leveraging the ROW_NUMBER() window function along with the PARTITION BY clause to effectively partition the data into groups.