Select for every column the first value that is NOT NULL-CodePudding

Table to select from:

 ------ ------ ------ ------ 
| col1 | col2 | col3 | col4 |
|______|______|______|______|
| NULL | foo  | NULL | NULL |
| bar  | NULL | NULL | NULL |
| NULL | NULL | NULL | baz  |
 ------ ------ ------ ------

Expected result:

 ------ ------ ------ ------ 
| col1 | col2 | col3 | col4 |
|______|______|______|______|
| bar  | foo  | NULL | baz  |
 ------ ------ ------ ------

Is there a way to do this using a wildcard, so without having to list all column names in the query?

Update:

There's no such thing as MAX(*) LIMIT 1, but maybe by combining COALESCE with a SELECT Field FROM (DESCRIBE <TableName>) that ships all the column names?

I already tried many things with no success, but there must be a generic solution without listing all the column names. It's no problem if it's hacky.

Update 2:

Getting closer... Column names I can query using: SELECT column_name FROM information_schema.columns WHERE table_name = '<TableName>'

CodePudding user response：

Hello Please test this:

VERSION-1

SELECT T1.Col1,T2.Col2,COALESCE(T1.Col3) AS Col3, T4.Col4
FROM TblNoNull T1
JOIN (SELECT(Col2) FROM TblNoNull) T2 ON T1.Col1 <>T2.Col2
JOIN (SELECT(Col4) FROM TblNoNull) T4 ON T2.Col2 <>T4.Col4;

If we test it, It returns:

VERSION-2

SELECT DISTINCT T1.Col1,T2.Col2,T3.Col3, T4.Col4
FROM TblNoNull T1
JOIN (SELECT(Col2) FROM TblNoNull) T2 ON T1.Col1 <>T2.Col2
JOIN (SELECT(Col3) FROM TblNoNull) T3 ON T3.Col3 IS NULL
JOIN (SELECT(Col4) FROM TblNoNull) T4 ON T2.Col2 <>T4.Col4;

Let's test it:

CodePudding user response：

Tested on MySQL 8.0.29:

select
  first_value(col1) over (order by case when col1 is null then 1 else 0 end, id) as col1,
  first_value(col2) over (order by case when col2 is null then 1 else 0 end, id) as col2,
  first_value(col3) over (order by case when col3 is null then 1 else 0 end, id) as col3,
  first_value(col4) over (order by case when col4 is null then 1 else 0 end, id) as col4
from mytable
limit 1;

Result:

 ------ ------ ------ ------ 
| col1 | col2 | col3 | col4 |
 ------ ------ ------ ------ 
| bar  | foo  | NULL | baz  |
 ------ ------ ------ ------