How to Retrieve Monthly Top 3 Order Amounts in MySQL 5.7 & 8.0

Problem Description

Requirement: query each month the order_amount ranking and return the top 3 records. For example, for 2019-02 the result should contain the three rows with the highest order amounts.

Solution

MySQL 5.7 and MySQL 8.0 have different approaches.

1. MySQL 5.7

First write a query that orders records by year, month extracted from order_date and order_amount in descending order.

Add a new column order_rank to store the rank of each record within its month.

The query uses user-defined variables @current_month and @order_rank. The assignment operator := creates the variables dynamically without needing a SET statement.

Logic:

Extract the month from order_date and assign it to current_month so the month can be tracked.

Compare current_month with the month of the current row; if they match, increment order_rank by 1, otherwise reset it to 1.

After the sub‑query, a WHERE clause filters rows where order_rank ≤ 3, yielding the top three orders per month.

Final MySQL 5.7 statement: