MySQL 5.5 - fill gaps with value from above

Question

I have a table containing global sales data at different reporting dates like this:

+------------+------+------------+---------+ | Closed | Open | Plan | Station | +------------+------+------------+---------+ | 2018-10-23 | NULL | NULL | A | | 2018-10-22 | NULL | NULL | NULL | | 2018-10-22 | NULL | NULL | B | | 2018-10-22 | NULL | NULL | NULL | | NULL | NULL | 2018-10-23 | C | | NULL | NULL | 2018-10-22 | NULL | | NULL | NULL | 2018-10-22 | NULL | +------------+------+------------+---------+ CREATE TABLE Orders (Closed DATE, Open DATE, Plan DATE, Station Char); insert into Orders values ("2018-10-23",NULL,NULL, "A"); insert into Orders values ("2018-10-22",NULL,NULL, NULL); insert into Orders values ("2018-10-22",NULL,NULL, "B"); insert into Orders values ("2018-10-22",NULL,NULL, NULL); insert into Orders values (NULL,NULL,"2018-10-23", "C"); insert into Orders values (NULL,NULL,"2018-10-22", NULL); insert into Orders values (NULL,NULL,"2018-10-22", NULL);

And I want to fill Station Column with the last know value to get the below desired results.

+------------+------+------------+---------+ | Closed | Open | Plan | Station | +------------+------+------------+---------+ | 2018-10-23 | NULL | NULL | A | | 2018-10-22 | NULL | NULL | A | | 2018-10-22 | NULL | NULL | B | | 2018-10-22 | NULL | NULL | B | | NULL | NULL | 2018-10-23 | C | | NULL | NULL | 2018-10-22 | C | | NULL | NULL | 2018-10-22 | C | +------------+------+------------+---------+

Does the posted answer work ? Please give feedback. Thanks :) — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 15, 2018 at 15:40

Madhur Bhaiya · Accepted Answer · 2018-11-12 15:41:48Z

Assuming that there is a Primary Key column (let's say id) in your table, which can be used to define the "last known" value of Station. Remember that data is stored in unordered fashion, and without defining a particular order and/or Primary key, we cannot really define the "last known" value.

Coalesce() function will be used to handle null value for Station in a particular row. Then, we can use Correlated Subquery to determine the "last known" value.

SELECT t1.Closed, t1.Open, t1.Plan, COALESCE(t1.Station, (SELECT t2.Station FROM Orders AS t2 WHERE t2.id < t1.id AND t2.Station IS NOT NULL ORDER BY t2.id DESC LIMIT 1)) AS Station FROM Orders AS t1

Result

| Closed | Open | Plan | Station | | ---------- | ---- | ---------- | ------- | | 2018-10-23 | | | A | | 2018-10-22 | | | A | | 2018-10-22 | | | B | | 2018-10-22 | | | B | | | | 2018-10-23 | C | | | | 2018-10-22 | C | | | | 2018-10-22 | C |

View on DB Fiddle

Collectives™ on Stack Overflow

MySQL 5.5 - fill gaps with value from above

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related