SQL: Get the most frequent value for each group

Question

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.

for example i have

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

And i want to get most frequent value of each Time

Time    | Value
4:35:49 | 122
4:35:50 | 121
4:35:51 | 132

Thanks in advance

Remark I need to get the same result of this Excel solution : Get the most frequent value for each group

** MY SQL Solution **

I found a solution(Source) that works fine with mysql but i can't get it to work in ms-access:

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`

How is group determined? By same time value? – Parfait Jan 17 '16 at 23:37 — Parfait, Jan 17 '16 at 23:37
Yes each group has a specific time value – Horthe92 Jan 17 '16 at 23:43 — Horthe92, Jan 17 '16 at 23:43
@Shadow Check my last update – Horthe92 Jan 18 '16 at 00:00 — Horthe92, Jan 18 '16 at 00:00

score 6 · Answer 1 · answered Jan 18 '16 at 00:14

Consider an INNER JOIN to match the two derived table subqueries rather than a list of subquery select statements matched with WHERE clause. This has been tested in MS Access:

SELECT MaxCountSub.`Time`, CountSub.`Value`    
FROM    
     (SELECT myTable.`Time`, myTable.`Value`, Count(myTable.`Value`) AS CountOfValue
      FROM myTable
      GROUP BY myTable.`Time`, myTable.`Value`) As CountSub

INNER JOIN 

     (SELECT dT.`Time`, Max(CountOfValue) As MaxCountOfValue
      FROM
           (SELECT myTable.`Time`, myTable.`Value`, Count(myTable.`Value`) AS CountOfValue
            FROM myTable
            GROUP BY myTable.`Time`, myTable.`Value`) As dT
      GROUP BY dT.`Time`) As MaxCountSub

ON CountSub.`Time` = MaxCountSub.`Time` 
AND CountSub.CountOfValue = MaxCountSub.MaxCountOfValue

Gouda Elalfy · Answer 2 · 2016-01-18T04:11:45.487

1

you can do this by query like this:

select time, value 
from (select value, time from your_table 
group by value , time
order by count(time) desc
) temp where temp.value = value
group by value

edited Jan 18 '16 at 04:11

answered Jan 18 '16 at 00:08

Gouda Elalfy

6,888
1
26
38

I'm not quite sure that this example is correct. 1) The most external "time' must be either listed in "group by" or be an aggregated value to work. 2) In "where temp.value = value" "value" from "temp" sub-query is compared with itself, hence it would always be true and makes no sense. 3) I generally don't understand the logic within the code and how would it return the most frequent occurrence of "value" in every "time" group. – Igor Nikiforov Oct 03 '21 at 22:39

SQL: Get the most frequent value for each group

2 Answers2