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.
