CODEWITHSUNDEEP
sql
Jay P
Jay P

Reputation: 29

Finding the corresponding row value of highest row number in another column

I have a table similar to this one below. I want to create New_Column that finds the highest number for the selected date, and returns the date, while returning null for all of the other numbers. I was thinking of adding in a case when, but wasn't sure how to incorporate it.

Date         Number   New_Column
2021-03-18     1          null
2021-03-18     8          null
2021-03-18     20       2021-03-18
2021-04-18     1          null
2021-04-18     7          null
2021-04-18     11      2021-04-18

Upvotes: 1

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use window functions:

select t.*,
       (case when number = max(number) over (partition by date)
             then date
        end) as new_column
from t;

Note: If there are ties for the highest value and you only want one to be marked

select t.*,
       (case when row_number() over (partition by date order by number desc) = 1
             then date
        end) as new_column
from t;

Upvotes: 1

Related Questions