Re: tricky SQL query (currently handled through PL/SQL)

From: Ralf <db.2.gemini_at_spamgourmet.com>
Date: 12 Jun 2003 04:13:20 -0700
Message-ID: <51a9e702.0306120313.514ac0f6_at_posting.google.com>


Hello, bung ho,

yours is truly the better solution and could with some more code also work with my_name like mine.

Toms solution, that was my source, was for "How to get min and max of consecutive numbers" (search for it in asktom.orcale.com for the sample) and is really overstated for this problem

> select cd, mn, mx
> from
> (select cd,
> decode(b, cd, '', dt) mn,
> decode(a, cd, lead(dt) over (order by dt), dt) mx
> from
> (select my_code cd,
> my_date dt,
> lag(my_code) over (order by my_date) b,
> lead(my_code) over (order by my_date) a
> from my_table_v1)
> where b != cd or a != cd or b is null or a is null)
> where mn is not null
>
>
> i'm quite sure someone can come up with something better.

Let's see, if where is anybody

Greetings

Ralf Received on Thu Jun 12 2003 - 13:13:20 CEST

Original text of this message