0

I have a table in SQL Workbench (MySQL Workbench) like the following:

Customer_id || Rep_id || Date_of_service
        111 ||    654 || 1/1/2017
        111 ||    673 || 10/20/2018
        112 ||    655 || 1/1/2017
        112 ||    655 || 4/1/2018
        112 ||    654 || 5/10/2018

And I want the following back a list of unique customer_ids with most frequent rep_ids and in the case of a tie, most recent rep_id. Example:

Customer_id || Rep_id || Date_of_service
        111 ||    673 || 10/20/2018
        112 ||    655 || 4/1/2018

I tried the following code:

create table most_recent_rep as
select customer_id, rep_id, max(date_of_service) as most_recent_date from my_table
group by customer_id, rep_id;

But this did not give me a list of unique customer IDs. I also tried

create table most_recent_rep as
select customer_id, rep_id, date_of_service from 
(select customer_id, max(date_of_service) as most_recent_date from my_table) as b
on b.customer_id = a.customer_id and b.most_recent_date = a.date_of_service;

But this created fewer results than I know there to be.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
brightman1309
  • 33
  • 7

1 Answers1

0

One method uses a correlated subquery:

select t.*
from my_table t
where t.date_of_service = (select max(t2.date_of_service)
                           from my_table t2
                           where t2.customer_id = t.customer_id
                          );

For performance, you want an index on (customer_id, date_of_service).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This does not return a full list of customer IDs. I know that I have 400k eligible unique customers, but this returns only 100k. – brightman1309 Jan 07 '20 at 16:15
  • @brightman1309 . . . This would only miss customers where all values of `date_of_service` are `NULL` or `customer_id` is `NULL`. – Gordon Linoff Jan 07 '20 at 16:32