find most repeated value without using group by

user_14385718 0
Viewed 29

I want to find the most repeated category in products without using group by

SELECT 
   MAX(COUNT(c.CategoryID) OVER (PARTITION BY c.CategoryName)) 
FROM [Categories] c left join Products p on c.CategoryID=p.CategoryID`
sql database
1 Answers

you cannnot use MAX

but you can Order It

SELECT TOP 1
   COUNT(c.CategoryID) OVER (PARTITION BY c.CategoryName) countr 
FROM [Categories] c left join Products p on c.CategoryID=p.CategoryID
ORDER BY countr DESC
user_51935360
Related