Sorry for the lack of a more appropriate title. The summary of my problem is: i run a query and I get some results; then I create a view using this query, and I run the same query on the view, and get different results. Details follow.
On the original table the analytical data is as follows: # SELECT id,experiment,insertedon,score FROM data WHERE id=1160; id | experiment | insertedon | score --------+----------------------+---------------------+-------- 1160 | alpha | 2012-08-19 01:01:12 | 220.69 1160 | alpha | 2012-08-19 01:01:22 | 220.69 1160 | beta | 2012-08-19 01:01:31 | 220.7 1160 | beta | 2012-08-19 01:01:42 | 220.7 1160 | beta | 2012-08-19 01:01:54 | 220.7 My query of interest using windowing functions is: # SELECT id, experiment, first_value(insertedon) OVER (PARTITION BY score, id) AS first_insertedon, score FROM data WHERE id=1160; id | experiment | first_insertedon | score | --------+----------------------+---------------------+--------+---------- 1160 | alpha | 2012-08-19 01:01:12 | 220.69 | 1160 | alpha | 2012-08-19 01:01:12 | 220.69 | 1160 | beta | 2012-08-19 01:01:31 | 220.7 | 1160 | beta | 2012-08-19 01:01:31 | 220.7 | 1160 | beta | 2012-08-19 01:01:31 | 220.7 | So far so good. I then create the view on this last query without the WHERE condition: # CREATE VIEW clustered_view AS SELECT id, experiment, first_value(insertedon) OVER (PARTITION BY score, id) AS first_insertedon, score FROM data; I see the view created correctly and its definition is according to the mentioned SQL query. I now select from the view adding the WHERE condition: # SELECT * from clustered_view WHERE id=1160; id | experiment | first_insertedon | score | --------+----------------------+---------------------+--------+ 1160 | alpha | 2012-08-19 01:01:12 | 220.69 | 1160 | alpha | 2012-08-19 01:01:12 | 220.69 | 1160 | beta | 2012-08-19 01:01:54 | 220.7 | 1160 | beta | 2012-08-19 01:01:54 | 220.7 | 1160 | beta | 2012-08-19 01:01:54 | 220.7 | As you see, the 'first_insertedon' timestamp for the experiment 'beta' is no longer the first of the timestamps i.e. '2012-08-19 01:01:31' as the original query's results gave correctly, but it's now the last one i.e. '2012-08-19 01:01:54' Any ideas? Missing the obvious? TIA, Thalis K.