Retrieve last non-null record of every column for each record_id in MySQL

Question

I have this MySQL table named records. Below is its contents.

id  record_id   Data1   Data2   Time 
1   1           null    1       1/1/16
2   1           1       null    1/3/16
3   1           2       null    1/4/16
4   1           null    3       1/5/16
5   2           1       null    2/1/16
6   2           1       null    2/3/16
7   2           7       null    2/4/16
8   2           null    5       2/5/16

I would like to have a MySQL query to retrieve the last non-null record of each column for each record_id. The result would look something like;

record_id   Data1   Data2   Time 
1           2       3       1/5/16
2           7       5       2/5/16

The tricky part to this problem is that multiple columns are involved.

Answer 1

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT record_id, MAX(Time) AS Time
    FROM yourTable
    GROUP BY record_id
) t2
    ON t1.record_id = t2.record_id AND
       t1.Time = t2.Time

If you simply want the greatest value for the data and time columns, then see the answer given by @Matt. But your language makes it unclear what you really want.

Update:

Something like this might give the results you want:

SELECT a.record_id,
       a.Data1,
       b.Data2,
       c.Time
FROM
(
    SELECT t1.record_id,
           t1.Data1
    FROM yourTable t1
    INNER JOIN
    (
        SELECT record_id,
               MAX(CASE WHEN Data1 IS NULL THEN 0 ELSE id END) AS Data1Id
        FROM yourTable
        GROUP BY record_id
    ) t2
        ON t1.record_id = t2.record_id AND
           t1.Id = t2.Data1Id
) a
INNER JOIN
(
    SELECT t1.record_id,
           t1.Data2
    FROM yourTable t1
    INNER JOIN
    (
        SELECT record_id,
               MAX(CASE WHEN Data2 IS NULL THEN 0 ELSE id END) AS Data2Id
        FROM yourTable
        GROUP BY record_id
    ) t2
        ON t1.record_id = t2.record_id AND
           t1.Id = t2.Data2Id
) b
    ON a.record_id = b.record_id
INNER JOIN
(
    SELECT t1.record_id,
           t1.Time
    FROM yourTable t1
    INNER JOIN
    (
        SELECT record_id,
               MAX(CASE WHEN Data2 IS NULL THEN 0 ELSE id END) AS TimeId
        FROM yourTable
        GROUP BY record_id
    ) t2
        ON t1.record_id = t2.record_id AND
           t1.Id = t2.TimeId
) c
    ON a.record_id = c.record_id

Demo Here:

SQLFiddle

Answer 2

This one may solve your problem:

select 
  record_id,
  substring_index(group_concat(Data1 order by Time desc), ',', 1) Data1,
  substring_index(group_concat(Data2 order by Time desc), ',', 1) Data2,
  substring_index(group_concat(Time  order by Time desc), ',', 1) Time
from records
group by record_id
;

It may not be as fast as other answers, but is another version... give it a try. If you have a Data3 column in your table, you can copy/paste the Data1 column and just change all references of this column to the new one.

Just to explain how this works: the group_concat function concatenates all non-null values of a column with a separator (, by default). You can order the column before the concatenation. It works a bit like a window function in Oracle, Postgre, and others... The substring_index is just getting the first concatenated value, as the list is in a descending order of time.