CODEWITHSUNDEEP
mysqlsqlnull
Zerium
Zerium

Reputation: 17333

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:

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?

Upvotes: 3

Views: 2804

Answers (3)

AT-2017
AT-2017

Reputation: 3149

Use the following:

select @a:=null, @b:=null, @c:=null;
select A,B,C from (
    select @a:=coalesce(A,@a) as A, @b:=coalesce(B,@b) as B, @c:=coalesce(C,@) as C time
    from yourtable
    order by time asc
) as y order by time desc limit 1;

Updated The Post: 

  SELECT DISTINCT (
  SELECT col1
  FROM demo
  WHERE IFNULL(col1, 0) != 0 
  ORDER BY timestamp DESC 
  LIMIT 1
) col1, (
  SELECT col2
  FROM demo
  WHERE IFNULL(col2, 0) != 0 
  ORDER BY timestamp DESC 
  LIMIT 1
) col2, (
  SELECT col3
  FROM demo
  WHERE IFNULL(col3, 0) != 0 
  ORDER BY timestamp DESC 
  LIMIT 1
) col3
FROM demo

Table structure:

 col1    col2    col3  timestamp
  1       2        8   2016-09-02 10:00:00
  0       4        10  2016-09-04 12:00:00

Returns:

 col1    col2    col3 
   1       4      10

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Assuming you have a column, such as timestamp that specifies the ordering, then you can get the last id for each using:

select max(case when col1 is not null then timestamp end) as ts1,
       max(case when col2 is not null then timestamp end) as ts2,
       max(case when col3 is not null then timestamp end) as ts3
from t;

You can then get the rows you want using a join:

select t.*
from t join
     (select max(case when col1 is not null then timestamp end) as ts1,
             max(case when col2 is not null then timestamp end) as ts2,
             max(case when col3 is not null then timestamp end) as ts3
      from t
     ) tt
     on t.timestamp in (ts1, ts2, ts3)

Upvotes: 1

sstan
sstan

Reputation: 36503

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

Upvotes: 3

Related Questions