RE: Need help with a query

Thanks Gints,

Based on yours and Tom's example, I finally figured it out:

select min(start_date), max(end_date), min(num_units) units_per_week from (
select start_date, end_date, num_units, max(new_rn) over (order by start_date) grp
from (
select start_date, end_date, num_units
, case when lag(num_units) over (order by start_date) <> num_units then row_number() over (order by start_date) when row_number() over (order by start_date) = 1 then 1 else null end new_rn
from (
select min(start_air_date) start_date, max(end_air_date) end_date , count(*) num_units
from br_bbdef_unit
where brdbuy_def_id = 352864
group by start_air_date, end_air_date
)
))
group by grp
order by 1

What was initially throwing me off was my case statement where I originally had:
case when lag(num_units) over (order by start_date) = num_units (note = vs. <> above)

Until I realized that I only wanted a row_number() value when the num_units value changed and wanted nulls for the other cases in order for the max(new_rn) over (order by start_date) in the outer query to work properly.

Thanks again for the links.

Regards,

Alan Davey

> -----Original Message-----
> From: Gints Plivna [mailto:gints.plivna_at_gmail.com]
> Sent: Friday, February 29, 2008 9:05 AM
> To: Davey, Alan
> Cc: oracle-l
> Subject: Re: Need help with a query
>
>
> You can use your initial query output as starting point. Then mark
> different groups i.e. when NUM_UNITS changes it obviously is a new
> group. It can be done by comparing current value and previous value
> (analytic function lag).
> Then after identifying groups you can mark all records within a group
> with unique identifier.
> Then get last value for max(end_air_date) within a group using
> last_value analytic function.
> Then filter out only first rows within a group.
> All this can be done with a few levels of
> SELECT <cols> FROM (
> SELECT <cols> FROM (
> SELECT <cols> FROM (...)))
>
> For examples look into my paper Using Analytic Functions in Reports at
> http://gplivna.eu/papers/using_analytic_functions_in_reports.htm -
> Case three: getting the current value and first value in a hierarchy
> as well as asktom example here

>
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1394 636
> 9553642
>
> Probably this can somehow be done easier let's look at other member
> suggestions :)
>
> Gints Plivna
> http://www.gplivna.eu
>
> 2008/2/29, Davey, Alan <ddavey_at_harris.com>:
> > Hi,
> >
> > I have the following data:
> > >select min(start_air_date) start_date, max(end_air_date) end_date
> > , count(*) num_units
> > from br_bbdef_unit
> > where brdbuy_def_id = 352864
> > group by start_air_date, end_air_date
> > order by 1;

--
http://www.freelists.org/webpage/oracle-l