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 Regards, Yavor Ivanov -----Original Message----- From: jo_holvoet@xxxxxxxx [mailto:jo_holvoet@xxxxxxxx] Sent: Monday, November 10, 2008 12:12 PM To: Yavor Ivanov Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx Subject: Re: Analytic troubles 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@ste mo.bg> To Sent by: "oracle-l@xxxxxxxxxxxxx" oracle-l-bounce@f <oracle-l@xxxxxxxxxxxxx> reelists.org cc Subject 10/11/2008 10:43 Analytic troubles Please respond to Yavor_Ivanov@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