title: SQL: Get the most frequent value for each group
body: <p>Lets say that I have a table ( MS-ACCESS / MYSQL ) with two columns ( Time 'hh:mm:ss' , Value ) and i want to get most frequent value for each group of row.</p>
  
  <p>for example i have </p>
  
  <pre><code>Time    | Value
  4:35:49 | 122
  4:35:49 | 122
  4:35:50 | 121
  4:35:50 | 121
  4:35:50 | 111
  4:35:51 | 122
  4:35:51 | 111
  4:35:51 | 111
  4:35:51 | 132
  4:35:51 | 132
  </code></pre>
  
  <p>And i want to get most frequent value of each Time</p>
  
  <pre><code>Time    | Value
  4:35:49 | 122
  4:35:50 | 121
  4:35:51 | 132
  </code></pre>
  
  <p>Thanks in advance </p>
  
  <p><strong>Remark</strong>
  I need to get the same result of this <a href="https://stackoverflow.com/a/34843439/5802454">Excel solution : Get the most frequent value for each group</a></p>
  
  <p>** MY SQL Solution **</p>
  
  <p>I found a solution(<a href="http://www.techonthenet.com/oracle/questions/most_common.php" rel="nofollow noreferrer">Source</a>) that works fine with mysql but i can't get it to work in ms-access:</p>
  
  <pre><code>select cnt1.`Time`,MAX(cnt1.`Value`)
  from (select COUNT(*) as total, `Time`,`Value`
  from `my_table`
  group by `Time`,`Value`) cnt1,
  (select MAX(total) as maxtotal from (select COUNT(*) as total, 
  `Time`,`Value` from `my_table` group by `Time`,`Value`) cnt3 ) cnt2
  where cnt1.total = cnt2.maxtotal GROUP BY cnt1.`Time`
  </code></pre>