SQL Select highest value item

Question

SQL Select highest value item

Navigation

#1 by (4 votes)

2

I'm using the NorthWind database, and I want to select the category that has the largest number of items entered. 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'd like to know how to select the ID that has the most products by category.

sql sql-select sql-server

asked by anonymous 26.11.2017 / 20:23

1 answer

Left join with + equivalent in POSTGRES Doubts about routes JS angular x ASP.NET MVC

score 4 · Accepted Answer

If you order DESC you will have the largest number of products by 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

Just one more result!