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

Question

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?

olafhelper · Answer

GarthVader&nbsp;, the more correct query would be&nbsp;&nbsp;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;&nbsp;&nbsp;Or use&nbsp;ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn&nbsp;&nbsp;

garthvader · Answer

Thanks&nbsp;olafhelper&nbsp; I wasn't able to get it work unfortunately.

Forum Discussion

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

Share

Resources