SQL-- Max of count

Need SQL that provides the maximum number of items, & also the name of the maximum.

In the attached file, please provide SQL that outputs the following:

10  Fruit

This desired output is the max number of items in a category, and also that category name.
BBRRGGAsked:
Who is Participating?
 
Jeffrey Dake Senior Director of TechnologyCommented:
If that is the case I think the second example here should work for you.

http://www.w3resource.com/sql/aggregate-functions/max-count.php

Basically using a couple inner queries with a having clause
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No attachment
0
 
BBRRGGAuthor Commented:
File now attached.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
BBRRGGAuthor Commented:
0
 
Jeffrey Dake Senior Director of TechnologyCommented:
Select count(*)  , category  from table group by category order by count(*) desc;

This will give you all the counts, starting with the most.  I think that is what you are looking for.
0
 
BBRRGGAuthor Commented:
Almost.  

But later I need to do a Common Table Expression that joins on Category, and that join needs to filter the table to only fruit (or whichever category has the max # of items).  Thus, need to show only one record, which is the max category, such as:
10    Fruit
0
 
BBRRGGAuthor Commented:
To simplify my explanation, output needs to be only one record:

10  Fruit

Preferably one select statement, but if need a Common Table Expression to accomplish, that'd be OK too.
0
 
Jeffrey Dake Senior Director of TechnologyCommented:
Select category, count(*) from table group by category having count(*) = (select max(mycount) from (select category, count(*) from table group by category));

I think that is what you want. If not sorry I couldnt help
0
 
BBRRGGAuthor Commented:
Great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.