I have one table in my shop database with several items. Each item belongs into one category. I want to determine which category has the most distinct items. I don't know which query give me the result I look for. Any help would be appreciated. Thanks
The table looks something like this. I would like it to return food.
CodePudding user response：
Try with below:
SELECT COUNT(distinct item) as num,category FROM yourtable
GROUP BY category ORDER BY num DESC LIMIT 1
See DB Fiddle Demo
CodePudding user response：
To handle ties (if more than one category has the max and same amount of distinct items ), you could use rank function which is supported by most dbms.
Suppose we have the following data examples:
create table record(
id int primary key,
category varchar(100),
item varchar(100)
);
insert into record(id,category,item) values
(1,'school','Pen'),
(2,'school','Paper'),
(3,'food','Bubble Gum'),
(4,'food','Sandwich'),
(5,'food','Pepper'),
(6,'new_food','Bubble Gum'),
(7,'new_food','Sandwich'),
(8,'new_food','Pepper');
The result should be food and new_food because they have the same amount of distinct items which is the max as well.
select category
from ( select category,
rank() over( order by count(distinct item) desc ) as rnk
from record
group by category
) as tbl
where rnk=1;