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.