CODEWITHSUNDEEP
mysqlmariadb
tom
tom

Reputation: 2357

Select for every column the first value that is NOT NULL

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.

Upvotes: 0

Views: 875

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562398

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  |
+------+------+------+------+

Upvotes: 0

Ozan Sen
Ozan Sen

Reputation: 2615

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:

GGG

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:

AZAZAZAZAD

Upvotes: 1

Related Questions