SQL Select highest value item

Asked

Viewed 533 times

2

I’m using the Northwind database, and I want to select the category that has the largest number of registered items. I was able to list the category ID and the number of products it has with the following command: 

SELECT CategoryID, COUNT(ProductID) AS 'Produtos por categoria'
FROM Products
GROUP BY CategoryID

And the return was

CategoryID - Produtos por Categoria 1 12 2 12 3 13 4 10 5 7 6 6 7 5 8 12

I would like to know how to select the ID that has the largest number of products per category.

sql sql-server select-sql

1 answer

4


If you order DESC you will have the largest number of products per category:

SELECT TOP 1
      CategoryID, 
      COUNT(ProductID) AS 'Produtos por categoria'

FROM Products

GROUP BY CategoryID ORDER BY COUNT(ProductID) DESC


CategoryID - Produtos por Categoria
3                13

Only one major result!

  • Cool, it worked. But I need to get only the Categoryid 3 in case, I tried such a "Limit" but it didn’t work... Do you have any idea of a command that would allow me to take only the biggest?

  • SELECT TOP 1 , you will only get the first result! But if you have more than one result equal to 1, 2 and 8, only one will come

  • Thank you very much, it worked!!!

Browser other questions tagged sql sql-server select-sql

You are not signed in. Login or sign up in order to post.