How to take offset difference of a column without using `order by` and-CodePudding

Right now this code is doing what it needs to do. I just feel like that the function LAG should have an easier way to take basically OFFSET=-1, which is not possible so I have to resort to the current code. What I do not like is that is every time I need this functionality I need to add 2 lines: 1 in the SELECT statement, 1 in the ORDER BY statement. Is there a cleaner way to achieve the same result in 1 line?

I do not care if LAG is replaced by another function, I am just interested in the final result being the same.

WITH mytable AS (
    SELECT * FROM (
        VALUES
            (7),
            (6),
            (5),
            (4)
    ) AS t (time)
)
SELECT time,
       LAG(time) OVER (ORDER BY time ASC) AS lagged_time
FROM mytable
ORDER BY time DESC

CodePudding user response：

LAG(time) OVER (ORDER BY time ASC) == LEAD(time) OVER (ORDER BY time DESC). An ORDER BY clause is the only method to ensure output is sorted as needed. You must include the clause if a specific ordering is requried.