Re: Need help with a query

From: Gints Plivna <>
Date: Fri, 29 Feb 2008 16:04:31 +0200
Message-ID: <>

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

Gints Plivna

2008/2/29, Davey, Alan <>:
> 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;

Received on Fri Feb 29 2008 - 08:04:31 CST

Original text of this message