Search code examples
oracle11ginformatica-powercenter

how to rank data based on date grouped by year?


My input data looks like below:
Fisclyear__ date
2011 ______7/1/2010
2011 _____ 8/20/2010
2011 ______2/30/2011
2012 ______6/30/2011
2012 ______8/15/2011
2013 ______7/15/2013
2013 ______7/30/2013

I need to rank data based on date in asc order and grouped by year, my result should be like below

Fisclyear__ date ______rank
2011 ______7/01/2010___01
2011 ______ 8/20/2010___02
2011 ______2/30/2011___03
2012 ______6/30/2011___01
2012 ______8/15/2011___02
2013 ______7/15/2013___01
2013 ______7/30/2013___02


Solution

  • Use Oracle analytical function ROW_NUMBER () OVER PARTITION BY year ORDERBY date...i am couldn't find exact syntax but it works