Hi Tom, and thanks for the reply (I had the pleasure of meeting you 11 years ago in Pittsburgh; still a pleasure seeing your concise and helpful replies.)
In the end I went for a change of window function. Using "min(insertedon)" instead of "first_value(insertedon)" works correctly. Alternatively your suggestion of adding an "ORDER BY insertedon" clause also seems to work. It makes the first_value(insertedon) behave as min(insertedon). thanks again, Thalis K. On Mon, Aug 20, 2012 at 12:16 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Thalis Kalfigkopoulos <tkalf...@gmail.com> writes: > > # SELECT id, experiment, first_value(insertedon) OVER (PARTITION BY > score, > > id) AS first_insertedon, score FROM data WHERE id=1160; > > > [ versus ] > > > # CREATE VIEW clustered_view AS SELECT id, experiment, > > first_value(insertedon) OVER (PARTITION BY score, id) AS > first_insertedon, > > score FROM data; > > > # SELECT * from clustered_view WHERE id=1160; > > One possible reason these produce different results is in that in the > first case, the WHERE condition eliminates rows from the window > function's consideration. In the second case, it doesn't --- the WHERE > only filters the result rows from the view. However, the fact that "id" > is part of the partition list may insulate you from that; not quite sure > without seeing a more complete example. > > Another likely reason for trouble is that the window function seems > underspecified: without any ORDER BY clause, you are going to get a > random one of the insertedon values for the same score and id. It's > entirely likely that moving the WHERE clause would change the plan > enough to change the ordering of the rows seen by the window function. > Possibly you should be using min() instead of first_value(). > > regards, tom lane >
