How to update a column to latest data while referencing other columns

user_13038367 0
Viewed 36

I am very new to SQL and have been given a dataset to clean as part of my internship.

I reproduce part of the table below:

Period    Policy No  Client No  Premium
----------------------------------------
2021-01   88888888   56785678   $1234.00
2019-07   88888888   89237427   $4567.01
2018-05   12345678   11111111   $2322.99
2019-10   12345678   22222222   $4321.12

etc..

Basically, I need to create a new column, say, New Client No to reflect the latest client number, which is determined based on the Period column, for each Policy No. For example, the table above should look like the following:

Period    Policy No  Client No  New Client No  Premium
------------------------------------------------------
2021-01   88888888   56785678   56785678       $1234.00
2019-07   88888888   89237427   56785678       $4567.01
2018-05   12345678   11111111   22222222       $2322.99
2019-10   12345678   22222222   22222222       $4321.12

etc..

In other words, each policy number must correspond to only one client number and this client number must be the latest one, as determined by the period. Note that I cannot simply combine rows as I have many more columns, such as Premium, which contain data that I cannot "collate".

Is there an easy way to do this? I am certainly not looking forward to hard-coding them, as I have to rectify about 60 policy numbers. The dataset is also huge, with millions of rows, if that is relevant.

I have tried many things, such as playing around with joins, row_number() over (partition by) etc, but none seem to do the trick, or perhaps it is because my logic is not quite right and I also do not fully understand how they work.

As mentioned, I am very new to SQL, so any intuitive suggestions will be greatly appreciated! :)

P.S. I also heard that syntax sometimes differs based on the database system one uses, so for what it is worth, I am using Microsoft SQL Server!

sql-update sql-server sql
1 Answers

You can write a query using first_value:

select t.*,
       first_value(client_no) over (partition by policy_no order by period desc) as most_recent_client_no
from t;

If you want to actually change the data (which seems dangerous because it is so easy to get the most recent value), you can use:

with toupdate as (
      select t.*,
             first_value(client_no) over (partition by policy_no order by period desc) as most_recent_client_no
      from t
     )
update toupdate
    set client_no = most_recent_client_no
    where client_no <> most_recent_client_no;
user_11440350
Related