CODEWITHSUNDEEP
sqlsql-server
Revokez
Revokez

Reputation: 323

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.

Upvotes: 2

Views: 64

Answers (2)

Ashu
Ashu

Reputation: 482

here is how to do it using a group by in a subquery

select 
    ID,
    Name 
from 
(
    Select 
        ID,
        Name,
        Count(distinct(Name)) as [Count] 
    from tablename 
    group by 
        ID, 
        Name 
    having Count(distinct(Name)) = MAX(Count(distinct(Name)))
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 5

Related Questions