skip to Main Content

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

2

Answers


  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.

    Login or Signup to reply.
  2. 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