title: How to sort by most recent date given additional ID
body: <p>I have a table in SQL Workbench (MySQL Workbench) like the following:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Customer_id || Rep_id || Date_of_service
          111 ||    673 || 10/20/2018
          112 ||    655 || 4/1/2018
  </code></pre>
  
  <p>I tried the following code:</p>
  
  <pre><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;
  </code></pre>
  
  <p>But this did not give me a list of unique customer IDs. I also tried</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>But this created fewer results than I know there to be.</p>