Query the Column based on the value of another column

Hi I have a table structure like this

id-rank id name  value    rank
  1-1   1  abc somevalue1  1
  1-2   1  abc somevalue2  2
  1-3   1  abc somevalue3  3
  2-1   2  abc somevalue4  1
  3-1   3  abc somevalue5  1

Here id-rank is the concatenation of id and rank. Now if the rank is greater than 1 (say 2) then I want to extract value column data for id-rank 1-1 i.e somevalue1.

Here is the sql that I have written

select *
case when rank > 1 then (select value where id-rank = concat(id,'-',rank-1) from table)
else ''
from table

The expected output is:

id-rank id name  value    rank new_value
  1-1   1  abc somevalue1  1
  1-2   1  abc somevalue2  2   somevalue1  
  1-3   1  abc somevalue3  3   somevalue2  
  2-1   2  abc somevalue4  1
  3-1   3  abc somevalue5  1

My current output is

id-rank id name  value    rank new_value
  1-1   1  abc somevalue1  1
  1-2   1  abc somevalue2  2   null
  1-3   1  abc somevalue3  3   null
  2-1   2  abc somevalue4  1
  3-1   3  abc somevalue5  1

But what I get is null in new_value column. I guess this is because it is comparing the adjacent columns only. I am new to sql please let me know if anyone has a solution for this.

Thanks in advance

Answer

You can use lag() :

select t.*, prev_val
      (case when rank > 1 then prev_val end) as new_value
from (select t.*, 
            lag(value) over (partition by id order by rank) as prev_val
      from table t
     ) t;

EDIT : If you are looking for specific previous value then only lag() will be sufficient.

select t.*, 
       lag(value) over (partition by id order by rank) as new_value
from table t;