how to select not-null values from Oracle (optimized method)

Question

how can I select not null values from table ...

lets image table as follows:

master_id |     date_update     | name  |  dpt  | channel | source | active
---------------------------------------------------------------------------
    1     |   1/2/2015 15:43:21 | NULL  |  NULL |  NULL   |   NULL |    y
    1     |   1/2/2015 15:43:21 | NULL  |  FIN  |  NULL   |   NULL |    n
    1     |   1/2/2015 15:40:16 | Elvis |  NULL |  NULL   |   NULL |    n
    1     |   1/2/2015 15:26:38 | NULL  |  NULL |  CH1    |   NULL |    n
    1     |   1/2/2015 14:57:02 | NULL  |  NULL |  NULL   |    S1  |    n
    5     |   2/2/2015 15:28:02 | NULL  |  NULL |  CH2    |   NULL |    y
    5     |   1/2/2015 10:13:01 | Sarah |  NULL |  NULL   |   NULL |    n

The result I would like to get is:

master_id |     date_update     | name  |  dpt  | channel | source | active
---------------------------------------------------------------------------
    1     |   1/2/2015 15:43:21 | Elvis |  FIN  |  CH1    |   S1   |    y 
    5     |   2/2/2015 15:28:02 | Sarah |  NULL |  CH2    |   NULL |    y

You can notice, that DATE_UPDATE and ACTIVE column is the latest one ...

Which method is the most optimized one? I tried the combination of listagg (to merge rows into one) and then get the last informations via row_number() over() or max() over() but I am not sure if it is the best performance solution ... joins are also not the best solution (because there are 17 columns I need to merge) ..

Can we assume that for a given column and master_id group, the data will be sparse with respect to NULL values, meaning that there would be only one non NULL value in that column? Also, are you always choosing the latest date_update for each master_id group? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 2, 2016 at 8:38

Tim Biegeleisen · Accepted Answer · 2016-02-02 08:39:55Z

1

The Oracle MAX() aggregate function should ignore NULL values, meaning it should pick up on the non NULL value in each master_id group as being the "max" for that column/group.

SELECT master_id, MAX(date_update), MAX(name), MAX(dpt), MAX(channel), MAX(source),
    MAX(active)
FROM image
GROUP BY master_id

answered Feb 2, 2016 at 8:39

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mr.P Over a year ago

that's what I was thinking about :) but what is the performance?

Tim Biegeleisen Over a year ago

Rather than the performance of the query, I think the structure of the table might be the bigger concern. Your table is sparse, meaning that it has a lot of unused space. Do you plan to run this query often in a production setting?

Mr.P Over a year ago

I understand what you mean ... the problem is the incorrect application behavior :( I am trying to fix this "bug" with that query, which will serve as data_mart for reporting ... you know, kind of data consolidation ...

Collectives™ on Stack Overflow

how to select not-null values from Oracle (optimized method)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related