How to put value from another column instead of null for nearest date?-CodePudding

I do have next table structure on mysql server:

person_id   company_id  time_of_registration
12          333         2022-02-15 8:03:00
13          333         2022-02-15 8:04:00
14          NULL        2022-02-15 8:10:00
15          333         2022-02-15 8:10:00
16          NULL        2022-02-15 8:12:30
17          222         2022-02-15 8:14:00
18          NULL        2022-02-15 8:23:00
19          111         2022-02-15 11:04:00

I would like to put nearest company_id (by time_of_registration) if there is null in the company_id column.

Desired output is:

person_id   company_id  time_of_registration
12          333         2022-02-15 8:03:00
13          333         2022-02-15 8:04:00
14          333         2022-02-15 8:10:00
15          333         2022-02-15 8:10:00
16          222         2022-02-15 8:12:30
17          222         2022-02-15 8:14:00
18          222         2022-02-15 8:23:00
19          111         2022-02-15 11:04:00

Thanks in advance.

CodePudding user response：

You can use COALESCE() to default the value to a user variable which is set by a side-effect assignment expression. Something like the following:

SELECT person_id,
 @cmp := COALESCE(company_id, @cmp) AS company_id,
FROM MyTable
ORDER BY time_of_registration;