I have a derived table that looks like this
I’d like to get the top sales person in each quarter, so the result should look like
The tableit 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
2
Answers
With MySQL 8.3 you can utilize window functions to do this logic:
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. TheWHERE
clause then only selects the records with thern
of1
.Alternate approach: (using an older design pattern)
Newest Design pattern:
.