Best way to add row to result of SELECT statement that uses LAG function

Question

I have a table with timestamps, from which I need to select all intervals of timestamps, creating a resulting table with time slots. This result must also have times lots for "past - oldest entry" and "newest entry - future".

My solution for this problem was to use LAG, and then adding a final row (the future row) with another SELECT. Here's a simplification of the actual problem:

WITH dummy_values as (
    (select to_date('2019-08-10') some_timestamp from dual
     union
     select to_date('2019-08-12') some_timestamp from dual
     union
     select to_date('2019-08-14') some_timestamp from dual
     union
     select to_date('2019-08-16') some_timestamp from dual)
)
SELECT 
    nvl(lag(some_timestamp, 1) over (order by some_timestamp), to_date('1000-01-01')) before,
    some_timestamp after
FROM 
    dummy_values
UNION
    SELECT
        some_timestamp    before,
        to_date('9999-12-31') after
    FROM (  SELECT      *
            FROM        dummy_values
            ORDER BY    some_timestamp DESC)
    WHERE ROWNUM = 1
;

I'm curious to know if there is a better way to achieve this, namely without performing the second SELECT to the table to fetch the most recent value.

I cannot have the actual future and past values inserted into the table.

Answer 1

In the "WITH" clause, adding a union for the future date.

WITH dummy_values as ( (select to_date('2019-08-10') some_timestamp from dual union select to_date('2019-08-12') some_timestamp from dual union select to_date('2019-08-14') some_timestamp from dual union select to_date('2019-08-16') some_timestamp from dual union select to_date('9999-12-31') some_timestamp from dual) ) SELECT nvl(lag(some_timestamp, 1) over (order by some_timestamp), to_date('1000-01-01')) before, some_timestamp after FROM dummy_values

you can union the future dates before performing lag function.