How to update a column using min/max from a different column as conditional on hive

Question

So given the next table:

CREATE TABLE IF NOT exists my_data_base.stg (
    flag INT,
    incidence DATE);
insert into stg (flag, incidence) values (0, '2022-01-01'), (0, '2022-01-02'), (0, '2022-01-03'), (0, '2022-01-04');

I'd would like to change whichever row has the earliest date so that the flag column equals 1 while the rest stay the same, like this.

Incidence	Flag
2022-01-01	1
2022-01-02	0
2022-01-03	0
2022-01-04	0

Thank you very much in advance.

Answer 1

you really dont have to store the like this. But it depends on your requirement.
When you are loading the data, you can calculate the column like this -

SELECT 
flag, case when rs.rn =1 then 1 else 0 end as incidence --stamp least row to 1
FROM
( select flag, row_number() over( order by flag ) rn -- ordering the data so to find least row 
 from my_data_base.stg) rs

OR Else, like comment said, You can use above SQL to stamp the record to 1.