Unbounded PRECEDING? how to use it in this case.

Database
Enthusiast

Unbounded PRECEDING? how to use it in this case.

i have a table that has
orderid entry_ts worked_by
12345 2007-01-26 12:05:16 agent_1
12345 2007-01-27 12:08:20 agent_2
12345 2007-01-27 14:05:16 agent_3
12345 2007-01-28 17:07:17 agent_4
12345 2007-01-29 11:05:16 agent_5

i need to get the earlier entry_ts and worked_by to the current row how do i do that

result:
orderid entry_ts worked_by prev_entry_t pre_worked_by
12345 2007-01-26 12:05:16 agent_1
12345 2007-01-27 12:08:20 agent_2 2007-01-26 12:05:16 agent_1
12345 2007-01-27 14:05:16 agent_3 2007-01-27 12:08:20 agent_2
12345 2007-01-28 17:07:17 agent_4 2007-01-27 14:05:16 agent_3
12345 2007-01-29 11:05:16 agent_5 2007-01-28 17:07:17 agent_4

Thanks

2 REPLIES
Enthusiast

Re: Unbounded PRECEDING? how to use it in this case.

Something like this ?

SELECT ORDERID, ENTRY_TS, WORKED_BY
, MAX(ENTRY_TS) OVER(PARTITION BY ORDERID ORDER BY ENTRY_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) PREV_ENTRY_T
, MAX(WORKED_BY) OVER(PARTITION BY ORDERID ORDER BY ENTRY_TS ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) PRE_WORKED_BY
FROM DATA007
;

Enthusiast

Re: Unbounded PRECEDING? how to use it in this case.

Thanks joe. that was helpful.