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
If you don't want the
NULL value but something else instead for the first row, you can supply a default value for the
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