How to get a particular preceding value of a column in SQL [closed]

You can use a running, conditional MAX to get the latest datetime value when value = 2.

You can exclude the current row by using ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING

SELECT
  t.id,
  t.value,
  t.datetime,
  datetime2 = MAX(CASE WHEN t.value = 2 THEN t.datetime END)
       OVER (PARTITION BY t.id ORDER BY t.datetime ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM YourTable t;

SQL Fiddle