sqldb2

SQL Query, take last row to fill new column


I have a table that looks like this:

id company_id from_dt
111 345 200601
112 345 200603
112 379 200703
113 338 200808
113 309 200912
113 391 201009
114 318 200910

And I want my table to look like this:

id company_id from_dt to_dt
111 345 200601 NULL
112 345 200603 200703
112 379 200703 NULL
113 338 200808 200912
113 309 200912 201009
113 391 201009 NULL
114 318 200910 NULL

I just can't quite figure out the best way to format an SQL query to do this.


Solution

  • try this:

    SELECT 
        *,
        LAG(from_dt, 1, NULL) OVER(PARTITION BY id ORDER BY from_dt DESC) AS to_dt
    FROM orig_table
    

    assumes that the from_dt is of type integer