CODEWITHSUNDEEP
sqloracle-databasedatetimewindow-functions
Heisenberg
Heisenberg

Reputation: 5279

How to count by referring date in sql

I have tables like following.

table

product   customer  surrender_date
A            a       2020/5/1
B            a       2020/6/1
C            b       2019/7/1
D            b       2020/8/1
E            b       2020/9/1

First I'd like to group by customer

product   customer  surrender_date
A            a       2020/5/1
B            a       2020/6/1

Second I'd like to rank by refferring to surrender_date from the newestone

My desired result is like following

product   customer  surrender_date  rank
A            a       2020/5/1        2
B            a       2020/6/1        1

Therefore My whole desired result is following.

product   customer  surrender_date  rank
A            a       2020/5/1        2
B            a       2020/6/1        1
C            b       2019/7/1        3
D            b       2020/8/1        2
E            b       2020/9/1        1

Are there any way to achieve this?

As I've never referred to date, If someone has opinion,please let me know.

Upvotes: 1

Views: 34

Answers (1)

GMB
GMB

Reputation: 222412

You can use window functions:

select
    t.*,
    row_number() over(partition by customer order by surrender_date desc) rnk
from mytable

Notes:

  • I don't see what the question has to do with aggregation

  • depending on how you want to handle ties, you might be looking for rank() or dense_rank() instead of row_number()

Upvotes: 2

Related Questions