CODEWITHSUNDEEP
oracle-databasemergelistagg
Mr.P
Mr.P

Reputation: 1257

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

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) ..

Upvotes: 1

Views: 164

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522201

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

Upvotes: 1

Related Questions