0

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
JohnGagliano
  • 54
  • 1
  • 6

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon, you are right. I managed to confuse myself given the language of the question. If you know of a source that provides a succinct example of the rank function being segmented by multiple partitions (more than 1 field), could you please direct me to that. Much appreciated. – JohnGagliano Oct 15 '17 at 23:34
  • @JohnGagliano . . . I think you just need practice. The window functions affect the *entire* row, and `year` is just another column on the row. – Gordon Linoff Oct 16 '17 at 01:20