mariadb - SQL - Return amalgamated row of most recent content from matching rows

Question

I can't figure this out - I guess it's simple but I can't find a suitable method.

I believe it's called the 'Last non-null puzzle', but I can't follow the solutions online! At the moment I'm using PHP to parse through a returned array until variables are all full, but I'd prefer to do it in SQL. Using MariaDB 8 on Ubuntu.

ID increments as records are added. It could be a datetime.

If I have the following:

ID | Data1 | Data2 | Data3
1  | NULL  | Book  | NULL
2  | Pink  | NULL  | Cat
3  | NULL  | Book  | NULL
4  | Blue  | NULL  | NULL
5  | NULL  | Stool | Cat
6  | White | NULL  | NULL
7  | NULL  | NULL  | Bull

How would I return: White | Stool | Bull (the most 'recent' non-NULLs)?

Is there a way to select on id = '4' and return Blue | Book | Cat?

I've been trying for hours! I've seen some posts on amalgamation but those don't seem to apply. Sorry of this is trivial - I assume it is but I can't figure it out.

question from:https://stackoverflow.com/questions/65878382/sql-return-amalgamated-row-of-most-recent-content-from-matching-rows

Answer 1

please check the following code.

SELECT * FROM

(SELECT Data1 FROM test t2
 WHERE Data1 IS NOT NULL
 ORDER BY ID DESC LIMIT 1
) Data1,

(SELECT Data2 FROM test t2
 WHERE Data2 IS NOT NULL
 ORDER BY ID DESC LIMIT 1
) Data2,

(SELECT Data3 FROM test t3
 WHERE Data3 IS NOT NULL
 ORDER BY ID DESC LIMIT 1
) Data3