Revokez Revokez - 2 months ago 6
SQL Question

Grouping based on most common value

I'm having trouble trying to group based on the most common value.
The data that I have is:

ID Name
327 TypeA
327 TypeA
327 TypeA
327 TypeB
327 TypeB
327 TypeB
327 TypeB


In this case I would want to select ID and the Name "TypeB" as it is the most common value for that ID. So the output would look like:

ID Name
327 TypeB


Hopefully someone can help with this, thanks.

Answer

This is called the mode in statistics. Here is one method:

select id, name
from (select id, name, count(*) as cnt,
             row_number() over (partition by id order by count(*) desc) as seqnum
      from t
      group by id, name
     ) t
where seqnum = 1;
Comments