SQL: Get the most frequent value for each group
<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>