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.

I have run into this problem before. What I have usually done is to create a view to do the dirty work of choosing the maximum value, or whatever:

create view un_max_date_achievement_view as
select
 skill_id,
 user_id,
 quality_id,
 max(date_achieved) as max_date
from
 un_skill_achievements
group by
 user_id,
 skill_id,
 quality_id;

Once you have the views defined, just join with whatever table you need:

select
 a.*,
 ss.*,
 to_char(now(),'YYYY Month DD') as nice_date
from
 (un_standard_skills ss
 LEFT OUTER JOIN
  un_max_date_achievement_view a
 ON
  a.skill_id = ss.skill_id 
 )
where 
(a.quality_id = 4
OR
 a.quality_id is null)
and
(a.user_id = :user_id
 OR
 a.user_id is null)
AND
 ss.skill_id in ($skill_id_list)
order by
 ss.skill_number,
 ss.skill_letter

With Oracle this would look a little nicer.