Re: Analytic troubles

From: <jo_holvoet_at_amis.com>
Date: Mon, 10 Nov 2008 11:12:14 +0100
Message-ID: <OFA751B625.AF483501-ONC12574FD.0038025F-C12574FD.00380D74@amis.com>

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

   or name1 is null

mvg/regards

Jo

                                                                           
             Yavor Ivanov                                                  
             <Yavor_Ivanov_at_ste                                             
             mo.bg>                                                     To 
             Sent by:                  "oracle-l_at_freelists.org"            
             oracle-l-bounce_at_f         <oracle-l_at_freelists.org>            
             reelists.org                                               cc 
                                                                           
                                                                   Subject 
             10/11/2008 10:43          Analytic troubles                   
                                                                           
                                                                           
             Please respond to                                             
             Yavor_Ivanov_at_stem                                             
                   o.bg                                                    
                                                                           
                                                                           

                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 table:
select distinct name,

                (select min(date_from)
                   from table1 p2
                  where p2.name = p1.name
                    and p2.date_from > (select max(date_from)
                                          from table1 p3
                                         where p3.name != p2.name
                                           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)

Regards,
Yavor Ivanov
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Mon Nov 10 2008 - 04:12:14 CST