All Forums Database
31 Aug 2007
Unbounded PRECEDING? how to use it in this case.

i have a table that has orderid entry_ts worked_by12345 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 thatresult:orderid entry_ts worked_by prev_entry_t pre_worked_by12345 2007-01-26 12:05:16 agent_1 12345 2007-01-27 12:08:20 agent_2 2007-01-26 12:05:16 agent_112345 2007-01-27 14:05:16 agent_3 2007-01-27 12:08:20 agent_212345 2007-01-28 17:07:17 agent_4 2007-01-27 14:05:16 agent_312345 2007-01-29 11:05:16 agent_5 2007-01-28 17:07:17 agent_4Thanks

joedsilva 505 posts Joined 07/05
03 Sep 2007

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_BYFROM DATA007;

04 Sep 2007

Thanks joe. that was helpful.

You must sign in to leave a comment.