finding lag value for group of values

Question

I have table like below

value 
 10
 10
 10
 20
 20
 30
 30
 30
 40     
 50

I need to create an additional column with lagged values like below

value   lag_value 
  10      NULL
  10      NULL
  10      NULL
  20      10
  20      10
  30      20
  30      20
  30      20
  40      30      
  50      40

Answer 1

If I understand correctly, for your sample data you can use a correlated subquery:

select *,
  (select Max(value) from t t2 where t2.value < t.value) Lag_Value
from t
order by value;

Example Fiddle