Mysql Question posted in
The official MySQL documentation can be found here.

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

I have a derived table that looks like this enter image description here

I’d like to get the top sales person in each quarter, so the result should look like enter image description here

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Alternate approach: (using an older design pattern)

    WITH t1 as (another complicated SQL) 

SELECT a.Quarter, Sales, a.Salesperson 
FROM t1 a
INNER JOIN (SELECT max(sales) Isales, Quarter 
            FROM T1
            GROUP BY Quarter ) b
  on A.quarter = b.quarter
 and a.sales = B.Isales

    Newest Design pattern:

    • would be to use a lateral join something like.

    .

    WITH t1 as (another complicated SQL) 
SELECT a.Quarter, Sales, a.Salesperson 
FROM t1 a
INNER JOIN LATERAL (SELECT sales, Quarter 
            FROM T1
            ORDER BY Sales Desc
            LIMIT 1) b
  on A.quarter = b.quarter
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search