Faiz Faiz - 10 days ago 5
SQL Question

row_number partition result wrong

I am using ROW_NUMBER to rank countries by sales desc. Query looks like below

SELECT country, SUM(sales) AS sales,
ROW_NUMBER() OVER (PARTITION BY country ORDER BY SUM(sales) DESC) AS 'rank'
FROM table
GROUP BY country
ORDER BY sales DESC


For some reason the rank is one for all the countries.

Answer

If you partition by COUNTRY, the row_number reset to 1

SELECT country, 
       SUM(sales) AS sales, 
       ROW_NUMBER() OVER (ORDER BY SUM(sales) DESC) AS 'rank'
FROM table
GROUP BY country
ORDER BY sales DESC