Re: Need help with a query
Date: Fri, 29 Feb 2008 16:04:31 +0200
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
Case three: getting the current value and first value in a hierarchy
as well as asktom example here
Probably this can somehow be done easier let's look at other member suggestions :)
2008/2/29, Davey, Alan <ddavey_at_harris.com>:
> 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;