Forum OpenACS Development: using oracles "over (partition by .... order by ...)"

I just read more about oracle's "over (partition by ... order by ....)" and i got the impression that i could've used this a couple of times already, if i would've known more about it.

Let me give you a simple example: You want to join two tables, get some id columns, but want to select only those id values, where a certain column is minimum. The query could look like this:

select r.question_id, o.object_id
from survey_question_responses r, acs_objects o, survey_questions q
 where r.question_id = q.question_id
 and q.predefined_question_id = :predefined_question_id)
 and o.object_type = 'survey_response' 
 and r.response_id = o.object_id 
 and o.creation_date = (select min(o.creation_date)
                        from survey_question_responses r, acs_objects o, survey_questions q
                        where r.question_id = q.question_id
                        and q.predefined_question_id = :predefined_question_id)
                        and o.object_type = 'survey_response' 
                        and r.response_id = o.object_id)

Doing the same query (with all where-clauses) again in a subquery, just to get the minimum value of some column seemed pretty ugly to me. So i looked into the over () functionality and came up with the following (maybe it could be done even more elegant):

select distinct first_value(r.question_id) over (order by o.creation_date),
       first_value(o.object_id) over (order by o.creation_date)
from survey_question_responses r, acs_objects o, survey_questions q
 where r.question_id = q.question_id
 and q.predefined_question_id = :predefined_question_id
 and o.object_type = 'survey_response'
 and r.response_id = o.object_id

Bottom line: Maybe people should look more closely into this functionality to speed up some nasty queries (i guess the openacs is full of those). Afaik, something similar exists in pg. Even if not, there's always the possibility to do it the slow way there.