SQL query to get the maximum within a group, then attach another field to it

Question

I have a derived table that looks like this

Salesperson	Quarter	Sales
Jack	Q1	100
Jack	Q2	10
Jack	Q3	110
Jack	Q4	100
Mary	Q1	80
Mary	Q2	200
Mary	Q3	25
Mary	Q4	30

I'd like to get the top sales person in each quarter, so the result should look like

Quarter	Best Salesperon	Sales
Q1	Jack	100
Q2	Mary	200
Q3	Jack	110
Q4	Jack	200

The table is derived from a larger table that has many other fields, and the Sales column is the sum of individual sales for that salesperson in the original table, so I am actually using this in a WITH clause (e.g WITH t1 as (another complicated SQL) SELECT... But if I try to use

 SELECT Quarter, Max(Sales), Salesperson from t1 group by Quarter 

The query fails with an error because Salesperson is not in the GROUP BY clause

Answer 1

With MySQL 8.3 you can utilize window functions to do this logic:

SELECT Quarter, Sales, SalesPerson
FROM 
    (
         SELECT *, ROW_NUMBER() OVER (PARTITION BY QUARTER ORDER BY SALES DESC) as rn
         FROM your_table_here
    ) dt
WHERE rn = 1;

That ROW_NUMBER() bit will partition the records into chunks/partitions for each distinct quarter. It will then order them by Sales and add a row number starting at 1. The WHERE clause then only selects the records with the rn of 1.