10 most frequent value in sql column - linq to sql

Question

Let's say I have a table named details with these columns:

product id = int
product price = float
products' category = nvarchar(50)

This table has no primary key (or foreign key, if that's matter).

I want to perform a query which returns a list of strings which contains the 10 most frequent product's category (the categories with the most products belong to), in descending order.

How can I perform this query?

Answer 1

You could try something like this:

var tenMostFrequentCategories = products.GroupBy(product=>product.Category)
                                        .OrderByDescending(grp=>grp.Count())
                                        .Select(grp=>grp.Key)
                                        .ToList();

where I assume that products is your data context class.

Answer 2

Here's the SQL -

Select top 10 [Category], count(distinct productID) as NumProducts
from details
group by [category]
order by count(distinct productID) desc

The group by will count products by their category, then the top 10 returns only the ten most. If there are ties for 10th place, it will only pick one - if you need it to return both, let me know.