title: MYSQL retrieve row in one table where it occurs the most in another
body: <p>I'm tying to write a script that will detect any duplicate guests in my database so they can be merged.</p>
  
  <p>I'm try to select the guest id where they have the highest count of bookings in my bookings table as that is the guest i want to merge into. So if there are any duplicates it will return one row and the id will be of the guest with the most bookings. </p>
  
  <p>Not sure how I can do this?</p>
  
  <pre><code>select 
      count(*) as duplicates,
      guests.id as id,
      guest_name(null, null, guests.id) as guest_name
  from
      guests
          left join
      bookings_guests_link
          left join
      bookings ON bookings.id = bookings_guests_link.booking_id ON guests.id = bookings_guests_link.guest_id
          and bookings_guests_link.primary_client = 1
  group by title , firstname , lastname , street , city , postcode , state_id
  having duplicates &gt; 1
  order by count(bookings.id) desc , duplicates desc
  </code></pre>
  
  <p>Any help appreciated! Thank you</p>