Analytic troubles
- From: Yavor Ivanov <Yavor_Ivanov@xxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Nov 2008 11:40:43 +0200
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
Other related posts:
