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:13946369553642

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@xxxxxxxxxx>:
> 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

Other related posts: