title: SQL: Returning the most common value for each person
body: <p>EDIT: I'm using MySQL, I found another post with the same question, but it's in Postgres; I require MySQL. </p>
  
  <p><a href="https://stackoverflow.com/questions/344665/get-most-common-value-in-sql">Get most common value for each value of another column in SQL</a></p>
  
  <p>I ask this question after extensive searching of this site and others but have not found a result that works as I intend it to. </p>
  
  <p>I have a table of people (recordid, personid, transactionid) and a transaction table (transactionid, rating). I require a single SQL statement that can return the most common rating each person has. </p>
  
  <p>I currently have this SQL statement that returns the most common rating for a specified person id. It works and perhaps it may help others.</p>
  
  <pre><code>SELECT transactionTable.rating as MostCommonRating 
  FROM personTable, transactionTable 
  WHERE personTable.transactionid = transactionTable.transactionid 
  AND personTable.personid = 1
  GROUP BY transactionTable.rating 
  ORDER BY COUNT(transactionTable.rating) desc 
  LIMIT 1
  </code></pre>
  
  <p>However I require a statement that does what the above statement does for each personid in personTable.</p>
  
  <p>My attempt is below; however, it times out my MySQL server.</p>
  
  <pre><code>SELECT personid AS pid, 
  (SELECT transactionTable.rating as MostCommonRating 
  FROM personTable, transactionTable 
  WHERE personTable.transactionid = transactionTable.transactionid 
  AND personTable.personid = pid
  GROUP BY transactionTable.rating 
  ORDER BY COUNT(transactionTable.rating) desc 
  LIMIT 1)
  FROM persontable
  GROUP BY personid
  </code></pre>
  
  <p>Any help you can give me would be much obliged. Thanks.</p>
  
  <p><strong><code>PERSONTABLE</code></strong>:</p>
  
  <pre><code>RecordID,   PersonID,   TransactionID
  1,      Adam,       1
  2,      Adam,       2
  3,      Adam,       3
  4,      Ben,        1
  5,      Ben,        3
  6,      Ben,        4
  7,      Caitlin,    4
  8,      Caitlin,    5
  9,      Caitlin,    1
  </code></pre>
  
  <p><strong><code>TRANSACTIONTABLE</code></strong>:</p>
  
  <pre><code>TransactionID,  Rating
  1       Good
  2       Bad
  3       Good
  4       Average
  5       Average
  </code></pre>
  
  <p>The output of the SQL statement I am searching for would be:</p>
  
  <p><strong>OUTPUT</strong>:</p>
  
  <pre><code>PersonID,   MostCommonRating
  Adam        Good
  Ben         Good
  Caitlin     Average
  </code></pre>