问题描述:

I have a table like below.

Now I want a view where for each row, the values in column START_DATE should be the values in END_DATE of the previous row. (Ignore the 1st row alone for view creation which i will populate with junk value or null value).

Any suggestions please. Thanks in advance

网友答案:
select value1,
       lag(end_date) over (order by end_date) as start_date,
       end_date
from the_table;

If there is no "previous" row (based on the sort order defined by the order by) then the lag() function returns NULL.

If you don't want the NULL value but something else instead for the first row, you can supply a default value for the lag() function:

select value1,
       lag(end_date,1, DATE '2014-01-01') over (order by end_date) as start_date,
       end_date
from the_table;
网友答案:
SELECT t.ID, t.EndDate, Max(s.EndDate) as StartDate
FROM TableName t 
  LEFT JOIN TableName s ON t.EndDate> s.EndDate
GROUP BY t.ID, t.EndDate
相关阅读:
Top