SQL subqueries grouped by not null first

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.

What mean " but both were not what i searched for."?? .. the aggregation and group by is the simplest solution but if you don't want this try expalin why and what you are really looking for ??' — ScaisEdge, Mar 20 '19 at 10:09
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Mar 20 '19 at 10:37

score 1 · Answer 1 · answered Mar 20 '19 at 10:59

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.

score 0 · Answer 2 · answered Mar 20 '19 at 09:48

0

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

answered Mar 20 '19 at 09:48

Zaynul Abadin Tuhin

31,407
5
33
63

2

You can't just mark this down. You need to explain why this code does not answer your question. It returns the result set you have asked for. – iainc Mar 20 '19 at 10:33
1

It does so only in the charitable case he presented as an example. Taking into account what little he gave in the way of formal specfication (he wants the *FIRST* non-null value that appears, and if there is >1 of those, that won't necessarly need to be the MAX() ) there must be cases where your solution is off. – Erwin Smout Mar 20 '19 at 10:57

score 0 · Answer 3 · answered Mar 20 '19 at 11:09

0

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

answered Mar 20 '19 at 11:09

sanket

9
4

No need to use any aggregate function it's as simple as that..just use order by with nulls last. – sanket Mar 20 '19 at 11:11
while you are using max or any aggregate function with group by or partition by, it works on number type so it will gives the maximum or aggregate function result in number with partition by id or column use in partition. – sanket Mar 20 '19 at 11:16
So that if you are using any aggregate function on character type it can not gives you preferable output. – sanket Mar 20 '19 at 11:18

SQL subqueries grouped by not null first

3 Answers3