CODEWITHSUNDEEP
oracle-databasenull
Omri
Omri

Reputation: 1646

Ignore nulls in SQL

I'm using Oracle SQL and i need some help with a query. I have the following table:

Time     | Type | Value_A | Value_b | Loaded time sequence 
11:00:37 |   A  | Null    |  30     |      1
11:00:37 |   A  | 40      |  Null   |      2
11:00:37 |   B  | Null    |  20     |      3
11:00:37 |   B  | Null    |  50     |      4
11:00:38 |   C  | 50      |  Null   |      5
11:00:38 |   D  | Null    |  30     |      6
11:00:38 |   D  | 10      |  Null   |      7
11:00:38 |   D  | Null    |  5      |      8

For each Type i want to take the last loaded values of Value_a and value_b which are not NULL. Here is the expected output table:

Time     | Type | Value_A | Value_b
11:00:37 |   A  | 40      |  30    
11:00:37 |   B  | Null    |  50    
11:00:38 |   C  | 50      |  Null    
11:00:38 |   D  | 10      |  5

Please advise.

Upvotes: 0

Views: 328

Answers (2)

APC
APC

Reputation: 146179

Your test data suggests that the TIME and TYPE are linked, and that the values always rise with time. In which case this solution will work:

select Time
       , Type
       , max(Value_A) as Value_A
       , max(Value_B) as Value_B
from your_table
group by Time
         , Type

However, I think your posted data is unlikely to be representative, so you'll need a more sophisticated solution. This solution uses the LAST_VALUE() analytic function:

select distinct Time
       , Type
       , last_value(Value_A ignore nulls) 
           over (partition by time, type order by Loaded_time_sequence
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Value_A
       , last_value(Value_B ignore nulls) 
           over (partition by time, type order by Loaded_time_sequence
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Value_B
from t23
;

Here is the SQL Fiddle (although the site appears to be broken at the moment).

This may still not be the most correct answer. It depends on the actual data. For instance, what should happen when you have an entry for TYPE=A at TIME=11:00:39 ?

Upvotes: 3

David Faber
David Faber

Reputation: 12486

I would think it would be as simple as aggregating with MAX():

SELECT time, type, MAX(value_a) AS value_a, MAX(value_b) AS value_b
  FROM mytable
 GROUP BY time, type;

If time is a TIMESTAMP then you may want to group by TRUNC(time, 'SS')

Upvotes: 0

Related Questions