How to Select Most Frequent Value in a Column Per Each Id Group

Get most frequent value per group

Try this:

select top 1 with ties a.ID, a.OS,a.Device
from (
select d.ID, d.OS, d.Device, ROW_NUMBER () over (partition by d.OS, d.Device order by id) rnk
from DeviceOS2 d)a
order by a.rnk desc

Update

If you need the most frequent one for each ID:

select c.ID,c.OS,c.Device from (
select d.ID, d.OS, d.Device, ROW_NUMBER () over (partition by d.id, d.OS, d.Device order by id) rnk
from DeviceOS2 d)c
join 
(
select  a.ID,max(a.rnk) AS rnk
from (
select d.ID, d.OS, d.Device, ROW_NUMBER () over (partition by d.id, d.OS, d.Device order by id) rnk
from DeviceOS2 d)a
group by a.ID) a
on c.ID = a.ID and a.rnk = c.rnk

SQL SSMS return most frequent value for each personal id

Assuming you just want to create a report about the favorite fruit per person you can use this query:

with cte as (
    select p_id, fruit_bought, row_number() over (partition by p_id order by count(*) desc) as rn
    from t
    group by p_id, fruit_bought
)
select p_id, fruit_bought as favorite_fruit
from cte
where rn = 1

Find most frequent value in SQL column

SELECT
  <column_name>,
  COUNT(<column_name>) AS `value_occurrence` 

FROM
  <my_table>

GROUP BY 
  <column_name>

ORDER BY 
  `value_occurrence` DESC

LIMIT 1;

Replace <column_name> and <my_table>. Increase 1 if you want to see the N most common values of the column.

How to choose the most common value in a group related to other group in R?

Another dplyr strategy using count and slice:

library(dplyr)
DATA %>% 
    group_by(ID) %>% 
    count(VAR, CATEGORY) %>% 
    slice(which.max(n)) %>% 
    select(-n)

     ID VAR   CATEGORY
  <dbl> <chr> <chr>   
1     1 A     ANE     
2     2 C     BOA     
3     3 E     CAT     
4     4 F     DOG  

SQL Group-By most frequent

You can use a correlated subquery:

select distinct t1.PERSON, (
    select ATE
    from myTable t2
    where t2.PERSON = t1.PERSON
    group by ATE
    order by count(*) desc
    limit 1
) as ATE
from myTable t1

If you have ties, this query will pick one of the most eaten items "randomly".

With MySQL 8 or MariaDB 10.2 (both not stable yet) you will be able to use CTE (Common Table Expression)

with t1 as (
    select PERSON, ATE, count(*) as cnt
    from myTable
    group by PERSON, ATE
), t2 as (
    select PERSON, max(cnt) as cnt
    from t1
    group by PERSON
)
    select *
    from t1
    natural join t2

On ties this query may return multiple rows per group (PERSON).

Find the most common value per id

Wit row_number() window function:

select t.year, t.genre
from (
  select year, genre, 
    row_number() over (partition by year order by count(*) desc) rn
  from Oscar
  group by year, genre
) t
where t.rn = 1

See the demo.

Results:

| year | genre   |
| ---- | ------- |
| 2016 | Action  |
| 2017 | Romance |
| 2018 | Fantasy |
| 2019 | Fantasy |
| 2020 | Action  |

If you want ties in the results use rank() instead of row_number().

---

Related Topics