Re: Query to provide range of repeat values

From: VC <boston103_at_hotmail.com>
Date: Fri, 20 Feb 2004 02:27:29 GMT
Message-ID: <lgeZb.223825$U%5.1371253@attbi_s03>

Hello Joe,

"Joe Mellors" <joemellors_at_aol.com> wrote in message news:53566469.0402190803.4705150f_at_posting.google.com...
> I need a query to provide range data where rows have repeated data.
> I'll explain :
>
> Tables concerned are like this :
>
> Year Cost
> ---- ----
> 2000 10
> 2001 10
> 2002 20
> 2003 20
> 2004 20
> 2005 15
> 2006 10
>
> But I need to provide the costs in order of time but with the time as
> a range like this :
>
> Start End Cost
> ----- --- ----
> 2000 2001 10
> 2002 2004 20
> 2005 2005 15
> 2006 2006 10
>
> At first I thought I'd cracked it with :
>
> SELECT DISTINCT a.cost,
> (SELECT MIN(year) FROM mytable b WHERE b.cost = a.cost) AS start,
> (SELECT MAX(year) FROM mytable c WHERE c.cost = a.cost) AS end
> FROM mytable a
> ORDER BY start
>
> which would produce what I am looking for if none of the costs were
> repeated for a later time range (e.g. above where there are two ranges
> with a cost of 10)
>
> Is this possible ?

It's not too hard with analytics:

SQL> select * from t1;

      YEAR COST
---------- ----------

      2000         10
      2001         10
      2002         20
      2003         20
      2004         20
      2005         15
      2006         10

7 rows selected.

SQL> select distinct
  2 first_value(year) over (partition by rn order by year) first_year,

  3         last_value (year) over (partition by rn order by year
  4                rows between unbounded preceding and unbounded following)
last_year,
  5             cost

  6 from
  7 (
  8 select year, cost, max(rn) over (partition by cost order by year) rn

  9      from
 10      (
 11      select year,
 12             cost,
 13             case when year - lag(year) over (order by cost, year) != 1
 14                    then  row_number() over (order by cost)
 15                  when row_number() over (order by cost) = 1
 16                    then 1
 17             end rn
 18      from t1
 19      )

 20 )
 21 /

FIRST_YEAR LAST_YEAR COST
---------- ---------- ----------

      2000       2001         10
      2002       2004         20
      2005       2005         15
      2006       2006         10

SQL> Rgds.

VC Received on Thu Feb 19 2004 - 20:27:29 CST