CODEWITHSUNDEEP
sqlamazon-redshift
Jasmine
Jasmine

Reputation: 3

Find highest in a group and update a column based n that

Simplified example: My table has 4 columns: ID, Name, Amount and Parent. See image.

The parent column, in the actual table, is currently empty and needs to be populated with entries from the column "Name".

ID is a non unique column with multiple entries in the column 'Name' having the same ID.

The goal is to find the maximum value entry in the column "Amount" for any ID and populate the column "Parent" with the entry in column "Name". I will post a picture of an example table in comments

enter image description here

Upvotes: 0

Views: 42

Answers (1)

AlexYes
AlexYes

Reputation: 4208

The technique is to rank names within each ID by amount in a subquery and then join it back to the source table for update:

update my_table set "parent"="name"
from (
    select id,"name",row_number() over (partition by id order by "amount" desc)
    from my_table
) t
where my_table.id=t.id
and t.row_number=1

Upvotes: 2

Related Questions