[Solved] SQL statement for getting top 5

by


If I understand correctly, you want the top 5 countries that appear the most times in the ads table, and for each one of those countries you want to display its most frequent section.

The query below selects the top 5 countries by grouping the ads table by country and ordering the count and uses a subquery to select each country’s most popular section.

SELECT TOP 5 Country , (
    SELECT TOP 1 Section FROM ads a2
    WHERE a2.Country = a1.Country
    GROUP BY Section
    ORDER BY COUNT(*) DESC
) TopSection
FROM ads a1
GROUP BY Country
ORDER BY COUNT(*) DESC

To display the top 3 sections for the top 5 countries

SELECT TOP 5 Country , (
    SELECT TOP 1 Section FROM (
        SELECT Section, 
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rank
        FROM ads a2    
        WHERE a2.Country = a1.Country
        GROUP BY Section
    ) t1 WHERE rank = 1
) Top1Section, (
    SELECT TOP 1 Section FROM (
        SELECT Section, 
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rank
        FROM ads a2    
        WHERE a2.Country = a1.Country
        GROUP BY Section
    ) t1 WHERE rank = 2
) Top2Section, (
    SELECT TOP 1 Section FROM (
        SELECT Section, 
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rank
        FROM ads a2    
        WHERE a2.Country = a1.Country
        GROUP BY Section
    ) t1 WHERE rank = 3
) Top3Section
FROM ads a1
GROUP BY Country
ORDER BY COUNT(*) DESC

7

solved SQL statement for getting top 5