SQL subqueries grouped by not null first [duplicate]

Question

I have the following structure from a huge query with subqueries for every column. The goal is to have the non-NULL value for every column first.

+-----+--------+--------+--------+-------+
| id  |  val1  |  val2  |  val3  |  val4 |
+-----+--------+--------+--------+-------+
|   1 |  STR1  | NULL   | NULL   |  NULL |
|   1 |  NULL  | STR2   | NULL   |  NULL |
|   1 |  NULL  | NULL   | STR3   |  NULL |
|   1 |  NULL  | NULL   | NULL   |  STR4 |
|   2 |  STR1  | NULL   | NULL   |  NULL |
|   2 |  NULL  | STR2   | NULL   |  NULL |
+-----+--------+--------+--------+-------+

Desired result:

+-----+--------+--------+--------+-------+
| id  |  val1  |  val2  |  val3  |  val4 |
+-----+--------+--------+--------+-------+
|   1 |  STR1  | STR2   | STR3   |  STR4 |
|   2 |  STR1  | STR2   | NULL   |  NULL |
+-----+--------+--------+--------+-------+

My goal is to group by ID but have all non NULL-fields from the query first. I tried to use it with the MAX function as well as with GROUP_CONCAT but both were not what i searched for.

Do you have an idea on how to achieve this? Any help is highly appreicated.

Best endo

edit: put in the correct ordering together with max solved it. I'm sorry and a bit ashamed I bothered you with this.

Answer 1

In SQL, there is no such thing as "first" and "last" between different rows.

What you are asking for appears to be something like a COALESCE over some series of values as they appear in a column [for some particular subset of rows].

But since COALESCE by definition depends on an ordering, and SQL has no concept of such ordering [among rows], what you are asking for by definition cannot exist.

What you need to do is determine the ordering that you want and express that in SQL. Only after you have done that, can this word "first" that you use carry any meaning.

Answer 2

use aggregation

select id,max(val1) as val1,
max(val2) as val2,max(val3) as val3,
max(val4) as val4
from table group by id

Answer 3

select id,val1,val2,val3,val4 from table order by val1,val2,val3,val4 nulls last;