CODEWITHSUNDEEP
sqloracle-databasedatetimemaxmin
pizzafeet
pizzafeet

Reputation: 1

Fetch records who see a change in value over time

I have a SQL table with monthly records at the ID level. I'm interested in isolating only IDs whose VALUE has changed between the earliest month (July) and the latest (September).

| ID_FIELD   | MONTH       | VALUE        |
|:-----------|------------:|:------------:|
| ID1        | 01-JUL-20   |     3        |
| ID1        | 01-AUG-20   |     3        |
| ID1        | 01-SEP-20   |     3        |
| ID2        | 01-JUL-20   |     2        |
| ID2        | 01-AUG-20   |     2        |
| ID2        | 01-SEP-20   |     3        |
| ID3        | 01-JUL-20   |     1        |
| ID3        | 01-AUG-20   |     3        |
| ID3        | 01-SEP-20   |     3        |

Seems simple - but not sure the easiest way to tackle this. I'm using Oracle SQL Developer. Thanks!

Upvotes: 0

Views: 36

Answers (2)

Andrew Sayer
Andrew Sayer

Reputation: 2336

Gordon has given you a correct answer if you care about any changes whatsoever, but I'm interpreting your question differently - you only care about the id_field values if their value for their earliest month is different to their most recent, ignoring those that have had a value in between those two months.

First you want the earliest and most recent values:

select *
from (
select id_field, value, row_number() over (partition by id_field order by month) rn_asc, row_number() over (partition by id_field order by month desc) rn_desc
from    my_table
)
where 1 in (rn_asc, rn_desc)

You could then apply a having condition

select id_field
from (
select id_field, value, row_number() over (partition by id_field order by month) rn_asc, row_number() over (partition by id_field order by month desc) rn_desc
from    my_table
)
where 1 in (rn_asc, rn_desc)
group by id_field 
having count(distinct value) > 1

Or you could use a set operator like:

with sorted_table as 
 (select id_field, value, row_number() over (partition by id_field order by month) rn_asc, row_number() over (partition by id_field order by month desc) rn_desc
  from   my_table
 )
select distinct id_field
from  (select id_field, value 
       from   sorted_table
       where  rn_asc = 1
     minus
       select id_field, value 
       from   sorted_table
       where  rn_desc = 1
      )

Both options assume you only have a row per id_field per month value.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270643

If you want a change then you are looking for different values. So:

select id_field
from t
group by id_field
having min(value) <> max(value);

Upvotes: 2

Related Questions