BBRRGG
asked on
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.
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.
Jim Horn
No attachment
ASKER
File now attached.
ASKER
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.
This will give you all the counts, starting with the most. I think that is what you are looking for.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
10 Fruit
Preferably one select statement, but if need a Common Table Expression to accomplish, that'd be OK too.
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
I think that is what you want. If not sorry I couldnt help
ASKER
Great, thanks!