CODEWITHSUNDEEP
sqlrankanalytic-functions
JohnGagliano
JohnGagliano

Reputation: 54

SQL Analytic Functions: Rank over Multiple Partitions

A friend asked for assistance with the below query (see image for additional clarification):

"We need to generate the sales-rank that is based on the number of items sold, and is calculated by product. In other words for a given product, the year with the most number of items sold will be ranked 1, the year with the next number of items sold will be ranked 2, and so on"

I mailed him the following SQL, however he says that all rows come back with a rank of 1 

Select product, year, num_of_items_sold
RANK( ) OVER (PARTITION BY product, year ORDER BY num_of_items_sold) as sales_rank
from prod_sales

Am I missing something? Thanks.

sales-rank

Upvotes: 0

Views: 2892

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Given the question, it may seem surprising that year is not needed in the window function. All that it cares about is product and the number of items sold:

select product, year, num_of_items_sold
       rank() over (partition by product
                    order by num_of_items_sold desc
                   ) as sales_rank

Also note the descending sort for the order by.

Upvotes: 2

Related Questions