SELECT TOP with GROUP BY in Query Studio Returns Wrong Results
When experimenting with SQL in Salesforce Marketing Cloud, many developers use Query Studio to quickly test queries before moving them into Automation Studio Query Activities.
However, Query Studio has a small limitation: when using GROUP BY, the query often requires a TOP clause to execute properly. Without TOP, Query Studio may return an error or refuse to run the query.
For example, this query may fail in Query Studio:
SELECT
FirstName,
COUNT(*) AS Name_Count
FROM master_de
WHERE FirstName IS NOT NULL
GROUP BY FirstName
ORDER BY Name_Count DESCTo make it work in Query Studio, in order the run query we need to add TOP
SELECT TOP 20
FirstName,
COUNT(*) AS Name_Count
FROM master_de
WHERE FirstName IS NOT NULL
GROUP BY FirstName
ORDER BY Name_Count DESCThis is exactly where another hidden trap is waiting. The query will not necessarily return the top 20 results ordered by COUNT(*) DESC, as one might expect. Instead, it may return a random set of 20 grouped records with their counts, rather than the actual top values.
There is a fix for this and that is to use ROW_NUMBER() OVER
SELECT
rn AS Rank_Position,
FirstName,
Name_Count
FROM (
SELECT TOP 100 PERCENT
FirstName,
COUNT(*) AS Name_Count,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
FROM master_de
WHERE FirstName IS NOT NULL
GROUP BY FirstName
) ranked
WHERE rn <= 20Share With Others
