0

I have a table A like

LDTS LENGTH
2022-03-03 15:41:05.685 50
2022-03-03 15:42:08.203 NULL
2022-06-08 21:57:07.909 45
2022-06-28 21:58:43.558 70

I want to display the changes in length side by side in two columns, rather than vertical

So for example I would like a result like this

LDTS FROM_LENGTH TO_LENGTH
2022-03-03 15:42:08.203 50 NULL
2022-06-08 21:57:07.909 NULL 45
2022-06-28 21:58:43.558 45 70

where the timestamp (LDTS) would become the timestamp when the value changed (for example the length changed from 50 to null at time 2022-03-03 15:42:08.203). Is there a way I can achieve this?

I was trying something like:

select LDTS, LENGTH AS FROM_LENGTH
             lag(LENGTH) over (partition by LDTS order by LDTS) as TO_LENGTH
      from TABLE_1;
user18466310
  • 51
  • 7
  • This is right, except you dont want to partition by the timestamp or it will never lag anything (unless its duplicated). – Josh Jul 18 '22 at 16:32

1 Answers1

0

You're very close to the right answer. The problem is that you don't want to partition by anything, because the partition determines the higher-level grouping where the lagging will take place.

Since you don't seem to have a column to partition by, you can PARTITION BY NULL if you want.

You also need to add an argument to LAG to tell it how many rows to LAG. If you add -1 it will go forwards.

(You can confuse yourself if you LAG and then ORDER BY DESC, so I prefer to use -1 to lag forwards)

SELECT *,
        lag(LENGTH, -1) over (partition by NULL order by LDTS) as Lag_LENGTH_1
from My_First_Table

I use this SQL Generator to remind me of how to do the syntax for this sort of stuff.

Josh
  • 1,493
  • 1
  • 13
  • 24