Re: Analytic troubles
Date: Mon, 10 Nov 2008 10:15:46 +0000
Message-ID: <OFE1597149.38E361D5-ON802574FD.00383962-802574FD.0038605C@ons.gsi.gov.uk>
This does the trick for the example data
select date_from,name
from
(
select date_from,
name,
lag(name) over (order by date_from) last_name
from tab1
)
where last_name != name
or last_name is null;
DATE_FROM NAME
--------- ---------- 01-NOV-08 AAA 03-NOV-08 BBB 06-NOV-08 CCC 07-NOV-08 AAA
Cheers,
Ian
|---------+----------------------------->
| | Yavor_Ivanov_at_stemo|
| | .bg |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 10/11/2008 09:40 |
| | Please respond to |
| | Yavor_Ivanov |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: oracle-l_at_freelists.org | | cc: | | Subject: Analytic troubles | >--------------------------------------------------------------------------------------------------------------|
??????????????? 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¡K Can someone help me with this?
??????????????? (Database is 11g on Windows)
Regards,
Yavor Ivanov
This email was received from the INTERNET and scanned by the Government Secure Intranet anti-virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In case of problems, please call your organisationùþs IT Helpdesk. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk
Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. †Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^ Received on Mon Nov 10 2008 - 04:15:46 CST