SQL - How to select a value based on the max of another?

Question

If it have a table as below:

I want to get each shops top salesperson, their total sales and the stores manager. If i query and get the max total sales and group by shop title, how do I also get the correct salesperson?

What is your MySQL server version ? – Madhur Bhaiya Nov 27 '18 at 17:00 — Madhur Bhaiya, Nov 27 '18 at 17:00

score 1 · Answer 1 · answered Nov 27 '18 at 17:02

1

You can use a correlated subquery:

select t.*
from t
where t.total_sales = (select max(t2.total_sales)
                       from t t2
                       where t2.shop_title = t.shop_title
                      );

answered Nov 27 '18 at 17:02

Gordon Linoff

1,242,037
58
646
786

But this query doesn't show you the "other" top sales person name. – The Impaler Nov 27 '18 at 17:04
@TheImpaler . . . I don't know what you mean. This returns the top sales record for each shop, which seems to be what the OP wants. – Gordon Linoff Nov 27 '18 at 20:58

score 1 · Accepted Answer · edited Dec 16 '18 at 08:35

1

You could use inner join on subquery for max sales

select m.salesperson, t.max_sales, t.shop_title
from my_table m 
inner join (
    select max(sales) max_sales, shop_title
    from my_table
    group by shop_title
) t on t.shop_title = m.shop_title and t.max_sale = m.sales

edited Dec 16 '18 at 08:35

marc_s

732,580
175
1,330
1,459

answered Nov 27 '18 at 17:04

ScaisEdge

131,976
10
91
107

SQL - How to select a value based on the max of another?

2 Answers2