PaulHinostroza1 PaulHinostroza1 - 2 months ago 20
SQL Question

PLSQL Partition by

I would like to rank by latest year for multiple object id. I'm struggling to get this partition correct. First example is the output I'm getting. The second is the desired output.

GETTING THIS RESULT

OBJECTID,FA_YEAR,FA_PERIOD,AMOUNT, RANK
22208 2015 5 $257.00 1
22208 2015 6 $514.00 1
22208 2015 7 $771.00 1
22208 2015 8 $1,028.00 1
22208 2015 9 $1,285.00 1
22208 2015 10 $1,542.00 1
22208 2015 11 $1,799.00 1
22208 2015 12 $2,056.00 1
22208 2016 1 $257.00 1
22208 2016 2 $514.00 1
22208 2016 3 $771.00 1
22208 2016 4 $1,028.00 1


DESIRED OUTPUT

OBJECTID,FA_YEAR,FA_PERIOD,AMOUNT, RANK
22208 2015 5 $257.00 2
22208 2015 6 $514.00 2
22208 2015 7 $771.00 2
22208 2015 8 $1,028.00 2
22208 2015 9 $1,285.00 2
22208 2015 10 $1,542.00 2
22208 2015 11 $1,799.00 2
22208 2015 12 $2,056.00 2
22208 2016 1 $257.00 1
22208 2016 2 $514.00 1
22208 2016 3 $771.00 1
22208 2016 4 $1,028.00 1


EDIT: (from comment)

SELECT OBJECT_ID, FA_YEAR, FA_PERIOD, YTD_TOTAL,
ROW_NUMBER() OVER(PARTITION BY OBJECT_ID,FA_YEAR,FA_PERIOD ORDER BY OBJECT_ID,FA_YEAR,FA_PERIOD) AS rw
FROM YTD_DEPRECIATION

Answer

You seem to want a rank based only on year:

select OBJECT_ID, FA_YEAR, FA_PERIOD, YTD_TOTAL,
       DENSE_RANK() OVER (PARTITION BY OBJECT_ID ORDER BY FA_YEAR DESC) as rnk
from YTD_DEPRECIATION ;