How to structure simple subquery?

[cantona]

I have a query that contains the following fields parent_event_number, linked_event_number, week_pattern, event_start, event_end, Effective, eff_wk

Each parent_event_number may have more than one linked_event_number. What I would like to do is group my query on parent_event_number, then display the linked_event_number, week_pattern, event_start, event_end, Effective, eff_wk fields for the Max of eff_wk. This will give me the most up to date linked event for the parent event.

Whats the easiest way to do this? I know I probably need to insert a subquery, but I struggle to get my head around these!! Can anyone help? Examples of subqueries that i have looked at always seem to be overly simple and never quite explain to me what i need!

 

[Dagon]

There are two approaches. The subquery approach would go something like:

select linked_event_number, week_pattern, event_start, event_end, Effective, eff_wk fields
from table t1
where eff_wk = 
(select max(t2.eff_wk) from table t2
where t2.parent_event_number = t1.parent_event_number)

However, in my view, the better approach if you have the full version of Oracle, is to use a rank function:

select linked_event_number, week_pattern, event_start, event_end, Effective, eff_wk fields
from
(select linked_event_number, week_pattern, event_start, event_end, Effective, eff_wk fields, rank() over (partition by parent_event_number order by eff_wk desc) as rn
from table)
where rn=1

 

[IanWaterman]

and into your query

eff_wk =(select max(a.eff_wk)from table a where a.parent_event_number = linked_event_number
group by a.parent_event_number)

Think that will achieve what you want, if not just change clause or the group by.

Ian