Faiz Faiz - 1 year ago 65
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 Source

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