Having trouble getting a window function to sort correctly.
Given this data
consumer_id | move_date | history_timestamp
-------------+------------+----------------------------
12345| 2008-01-05 | 2007-12-11 06:02:26.842171
12345| 2008-02-29 | 2008-02-05 07:22:38.04067
12345| 2008-02-29 | 2008-07-11 09:03:42.44044
23456| 2009-01-01 | 2008-11-12 07:33:32.656658 <--
23456| 2009-01-28 | 2008-11-14 01:57:40.264335
23456| 2009-01-01 | 2008-12-04 17:14:20.279999 <--
23456| 2009-01-01 | 2008-12-31 00:33:37.204968
23456| 2009-01-01 | 2011-06-08 04:16:41.646521
34567| 2010-05-07 | 2010-06-08 05:14:43.842172
I'm trying to get the timestamp when the consumer last changed their move_date.
(Notice consumer_id 23456 set their move_date to 2009-01-01 then changed and
then changed it back. In the end, I want the timestamp from when they changed
it to 2009-01-01 the second time.)
My thought was to do an intermediary step to find the timestamp for each time
it switched. From there I can grab the max(timestamp) for each consumer.
Here's my query.
select ch.consumer_id, ch.move_date,ch.history_timestamp,
min(ch.history_timestamp) over w as start_time
from consumer_hist ch
window w as (partition by ch.consumer_id, ch.move_date order by
ch.consumer_id,ch.history_timestamp asc)
order by ch.consumer_id , ch.history_timestamp asc
I expect the following result
consumer_id | move_date | history_timestamp | start_time
-------------+------------+----------------------------+----------------------------
12345 | 2008-01-05 | 2007-12-11 06:02:26.842171 | 2007-12-11
06:02:26.842171
12345 | 2008-02-29 | 2008-02-05 07:22:38.04067 | 2008-02-05
07:22:38.04067
12345 | 2008-02-29 | 2008-07-11 09:03:42.44044 | 2008-02-05
07:22:38.04067
23456 | 2009-01-01 | 2008-11-12 07:33:32.656658 | 2008-11-12
07:33:32.656658 <--
23456 | 2009-01-28 | 2008-11-14 01:57:40.264335 | 2008-11-14
01:57:40.264335
23456 | 2009-01-01 | 2008-12-04 17:14:20.279999 | 2008-12-04
17:14:20.279999 <--
23456 | 2009-01-01 | 2008-12-31 00:33:37.204968 | 2008-12-04
17:14:20.279999
23456 | 2009-01-01 | 2011-06-08 04:16:41.646521 | 2008-12-04
17:14:20.279999
34567 | 2010-05-07 | 2010-06-08 05:14:43.842172 | 2010-06-08
05:14:43.842172
But the windowing function appears to be doing the partitioning first then the
order by.
So instead, I'm getting
consumer_id | move_date | history_timestamp | start_time
-------------+------------+----------------------------+----------------------------
12345 | 2008-01-05 | 2007-12-11 06:02:26.842171 | 2007-12-11
06:02:26.842171
12345 | 2008-02-29 | 2008-02-05 07:22:38.04067 | 2008-02-05
07:22:38.04067
12345 | 2008-02-29 | 2008-07-11 09:03:42.44044 | 2008-02-05
07:22:38.04067
23456 | 2009-01-01 | 2008-11-12 07:33:32.656658 | 2008-11-12
07:33:32.656658 <--
23456 | 2009-01-28 | 2008-11-14 01:57:40.264335 | 2008-11-14
01:57:40.264335
23456 | 2009-01-01 | 2008-12-04 17:14:20.279999 | 2008-11-12
07:33:32.656658 <--
23456 | 2009-01-01 | 2008-12-31 00:33:37.204968 | 2008-11-12
07:33:32.656658
23456 | 2009-01-01 | 2011-06-08 04:16:41.646521 | 2008-11-12
07:33:32.656658
34567 | 2010-05-07 | 2010-06-08 05:14:43.842172 | 2010-06-08
05:14:43.842172
Any suggestions to get the order by to occur first then the partition by or
maybe there another approach that I could use?
Dianna
