CODEWITHSUNDEEP
sqloracle-database
SASPYTHON
SASPYTHON

Reputation: 1621

DENSE_RANK () trick Oracle

I am confused about DENSE_Rank function

so I have a code like this

select ID, date
From TABLE1
order by date

so It executes like this

ID  DATE
1   10/25/2016
1   10/23/2015
1   10/22/2014
2   10/11/2015
2    5/24/2014
2    5/21/2014
2    3/23/2013
3   10/21/2016
3    9/20/2015

my code is

    select ID, Extract(MONTH FROM DATE), DENSE_RANK () OVER (PARTITION BY ID,Extract(MONTH FROM DATE order by DATE) rank
FROM TABLE

it shows

   ID  DATE RANK
    1   10  1
    1   10  2
    1   10  3
    2   10  1 
    2    5  1
    2    5  2
    2    3  1
    3   10  1
    3    9  2

but I would like to show like below

  ID  DATE RANK
    1   10  1
    1   10  1
    1   10  1
    2   10  1 
    2    5  2
    2    5  2
    2    3  3
    3   10  1
    3    9  2

how can I code to execute above

Thank you so much

Upvotes: 0

Views: 97

Answers (1)

MT0
MT0

Reputation: 167972

You want to order by the month (in descending order) rather than partition by them:

SELECT ID,
       Extract(MONTH FROM "DATE"),
       DENSE_RANK () OVER ( PARTITION BY ID
                            ORDER BY Extract(MONTH FROM "DATE") DESC ) rank
FROM   your_table

Upvotes: 3

Related Questions