How to get the top occurrence of rows to show SSMS 19

Copper Contributor

I am struggling to get the most occurring rows to show in SSMS 19.

ChatGPT gave me:

SELECT TOP 1 Product, COUNT(*) AS Occurrences

FROM Sales

WHERE Product IS NOT NULL

GROUP BY Product

ORDER BY Occurrences DESC;

 

This code runs but gives the incorrect results, I know by a manual filter in Excel that the results are wrong. 

Does anyone know what could be wrong?

2 Replies

@GarthVader , the more correct query would be

 

 

SELECT TOP 1 SUB.Product, SUB.Occurrences
   (SELECT Product, COUNT(*) AS Occurrences
    FROM Sales
    WHERE Product IS NOT NULL
    GROUP BY Product) AS SUB
ORDER BY SUB.Occurrences DESC;

 

 

Or use ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn

 

 

Thanks @olafhelper  I wasn't able to get it work unfortunately.