SQL Show the previous value until the given value changes

user_15055055 0
Viewed 485

I have a table ordered by ID with column Value.

| ID       | Value          |
| -------- | -------------- |
| 1        | 50             |
| 2        | 50             |
| 3        | 62             |
| 4        | 62             |
| 5        | 62             |
| 6        | 79             |
| 7        | 90             |
| 8        | 90             |

I would like to create another column Prev_Value that for each row of column Value takes the previous/preceding number that differs from the current row value, as in the table below.

Output table:

| ID       | Value          |Prev_Value     |
| -------- | -------------- |---------------|
| 1        | 50             |NULL           |
| 2        | 50             |NULL           |
| 3        | 62             |50             |
| 4        | 62             |50             |
| 5        | 62             |50             |
| 6        | 79             |62             |
| 7        | 90             |79             |
| 8        | 90             |79             |

Should I use modified LAG() function, the CROSS APPLY or nested CASE and what approach would be the most time-efficient? Any help would be appreciated.

Here are some references that unfortunately does not solve my problem: LAG(offset) until value is reached in BigQuery and SQL Server : select distinct until the value is changed

tsql sql-server sql
2 Answers

One method uses apply:

select t.*, tprev.value
from t outer apply
     (select top (1) tprev.*
      from t tprev
      where tprev.value <> t.value and
            tprev.id < t.id
      order by tprev.id desc
     ) tprev;

The above is not the most efficient method on a large dataset. For that, I would suggest getting the first time that a value changes and marking that.

select t.*,
        max(case when prev_value <> value then prev_value end) over (partition by grp) as prev_value
from (select t.*,
             sum(case when prev_value = value then 0 else 1 end) over (order by id) as grp
      from (select t.*,
                   lag(value) over (order by id) as prev_value
            from t
           ) t
     ) t;

Here is a db<>fiddle.

user_11440350

Here is one way to do it. (Check it live on this fiddle)

 select 
    t1.*, 
    t2.value prev_value  
from 
    t t1 
    left join t t2 on t2.id = (select max(id) from t where value<t1.value)
user_7361720
Related