think123 think123 - 2 months ago 10
MySQL Question

Find last not NULL value for each column in a MySQL table?

I have a table with columns

col1
,
col2
,
col3
. In each row, only one of these values is not null. I'd like to find the latest value for
col1
,
col2
and
col3
(obviously from three separate rows), where these are not
NULL
.

Here is a schema:


  • col1
    -
    INT

  • col2
    -
    INT

  • col3
    -
    INT

  • timestamp
    -
    DATETIME



Assume I have this data:

+------+------+------+------------------+
| col1 | col2 | col3 | timestamp |
+------+------+------+------------------+
| 1 | NULL | NULL | 15/09/2016 10:55 |
| NULL | 2 | NULL | 15/09/2016 10:56 |
| NULL | NULL | 3 | 15/09/2016 10:57 |
| 4 | NULL | NULL | 15/09/2016 10:58 |
+------+------+------+------------------+


I want the following results:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 4 | 2 | 3 |
+------+------+------+


How can I write a query to do this?

Answer
select
  (select col1 from tbl where col1 is not null order by timestamp desc limit 1) as col1,
  (select col2 from tbl where col2 is not null order by timestamp desc limit 1) as col2,
  (select col3 from tbl where col3 is not null order by timestamp desc limit 1) as col3
Comments