RE: Analytic troubles

From: Yavor Ivanov <>
Date: Mon, 10 Nov 2008 13:25:41 +0200
Message-ID: <BD17E2E69E17C64A9684C940EB580E0301102BC74E22@stemodc1.stemo.local>

        Thank you wall. I should be ashamed, I took the wrong path. I was trying to make it with FIRST_VALUE, and the windowing got too messy... While in just e few minutes 7 people gave me a solution with LAG.
        Here is the final solution (including date_to column form the same window):

select name, date_from, lead(prev_date, 1, null) over (order by date_from) date_to   from (select name, date_from,

               lag(name, 1, '*') over(order by date_from) prev_name,
               lag(date_to, 1, null) over(order by date_from) prev_date
          from table1)

 where name != prev_name

Yavor Ivanov

-----Original Message-----
From: [] Sent: Monday, November 10, 2008 12:12 PM To: Yavor Ivanov
Cc:; Subject: Re: Analytic troubles

select date_from, name from (
select a.date_from, name, lag(, 1) over (order by a.date_from) name1 from table1 a
where name <> name1

   or name1 is null



             Yavor Ivanov
   >                                                     To
             Sent by:                  ""
             oracle-l-bounce_at_f         <>

             10/11/2008 10:43          Analytic troubles

             Please respond to

                Hello, Gurus
                I’ve been thrown by the developers in an area, which I do
not visit frequently. Here is the case:
                Let’s say we have a table like this
Date_from                         Name
01.Nov.2008                      AAA
02.Nov.2008                      AAA
03.Nov.2008                      BBB
04.Nov.2008                      BBB
05.Nov.2008                      BBB
06.Nov.2008                      CCC
07.Nov.2008                      AAA
08.Nov.2008                      AAA

                I need the dates of every change. This is
Date_from         Name
01.Nov.2008                      AAA
03.Nov.2008                      BBB
06.Nov.2008                      CCC
07.Nov.2008                      AAA

                I can do it with regular SQL, but I make 3 reads of the
select distinct name,

(select min(date_from)

                   from table1 p2
                  where =
                    and p2.date_from > (select max(date_from)
                                          from table1 p3
                                         where !=
                                           and p3.date_from <
p1.date_from)) date_from
  from table1 p1
 I’m trying to implement it with analytic functions (and walk through the table only once), but I cannot define the window based on my knowledge. And window definition is not something very explained in the docs… Can someone help me with this?

(Database is 11g on Windows)

Yavor Ivanov
i0zX+n{+i^ Received on Mon Nov 10 2008 - 05:25:41 CST

Original text of this message