CODEWITHSUNDEEP
sqlaggregate-functions
dthree
dthree

Reputation: 20770

SQL Server - Aggregate by most rows with value

Given a table,

id     name
1      Joe
2      Bob
3      Joe
4      Joe
5      Bob

I would like to do an aggregate selection, returning the 'name' with the highest number of rows.

To illustrate, there are 3 'Joe's above, and only 2 'Bob's. Therefore, I would like to return 'Joe'.

I don't believe there is an aggregate function in SQL Server dedicated to doing this. count and max wouldn't do the job.

UPDATE

I suppose I could say:

 select max(names) as winning_name
   from (
         select name, 
                count(*) as names 
           from ultimate_table 
          group by name
        ) as grp

But is there a better way to do this without a subquery?

Upvotes: 1

Views: 73

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28761

select name from name_table
group by name
having count(*)= (select max(names)
   from (
         select 
                count(*) as names 
           from name_table 
          group by name
        ) as grp
)

You will have to use subquery if more than one name has maximumn number of occurrences.

SQL Fiddle Demo

create table name_table(id int,name varchar(10));
insert into name_table values(1,'Joe');
insert into name_table values(2,'Bob');
insert into name_table values(3,'Joe');
insert into name_table values(4,'Joe');
insert into name_table values(5,'Batman');
insert into name_table values(6,'Batman');
insert into name_table values(7,'Batman');

Output

NAME
----
Batman
Joe

Upvotes: 0

EkriirkE
EkriirkE

Reputation: 2305

First this would sort things by frequency:

SELECT name FROM name_table ORDER BY count(1) DESC

Then you simply limit the results to 1 row, depending on SQL flavour:

SELECT name FROM name_table ORDER BY count(1) DESC LIMIT 1

or

SELECT TOP 1 name FROM name_table ORDER BY count(1) DESC

Upvotes: 1

Related Questions