drop table t;
create table t(d date, v varchar2(1));
insert into t values ( sysdate+1, 'A');
insert into t values ( sysdate+2, 'A');
insert into t values ( sysdate+3, 'B');
insert into t values ( sysdate+4, 'A');
insert into t values ( sysdate+5, 'A');
insert into t values ( sysdate+6, 'A');
insert into t values ( sysdate+7, 'B');
insert into t values ( sysdate+8, 'B');
insert into t values ( sysdate+9, 'C');
insert into t values ( sysdate+10, 'C');
insert into t values ( sysdate+11, 'A');
insert into t values ( sysdate+12, 'A');
commit;
select distinct v, min(d) over(partition by c)d , count(c) over(PARTITION by c) cnt
from(
select d,v,sum(c) over(order by d) c
from (
select d,v,case when v <> nvl(lag(v,1) over(order by d),v) then 1 else 0 end c
from t))
order by 2;
---------------------------------------------
drop table tab_chart;
create table tab_chart(TYPE_OF_CHART varchar(100), DATE_OF_CHART date , TITLE varchar(100), ARTIST varchar(100));
insert into tab_chart values('Albums',to_date('27-02-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');
insert into tab_chart values('Albums',to_date('06-03-83','dd-mm-yyyy'),'WAR','U2');
insert into tab_chart values('Albums',to_date('13-03-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');
insert into tab_chart values('Albums',to_date('20-03-83','dd-mm-yyyy'),'THE HURTING','TEARS FOR FEARS');
insert into tab_chart values('Albums',to_date('27-03-83','dd-mm-yyyy'),'THE FINAL CUT','PINK FLOYD');
insert into tab_chart values('Albums',to_date('03-04-83','dd-mm-yyyy'),'THE FINAL CUT','PINK FLOYD');
insert into tab_chart values('Albums',to_date('10-04-83','dd-mm-yyyy'),'FASTER THAN THE SPEED OF NIGHT','BONNIE TYLER');
insert into tab_chart values('Albums',to_date('17-04-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');
insert into tab_chart values('Albums',to_date('24-04-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');
insert into tab_chart values('Albums',to_date('01-05-83','dd-mm-yyyy'),'LET''S DANCE','DAVID BOWIE');
insert into tab_chart values('Albums',to_date('08-05-83','dd-mm-yyyy'),'TRUE','SPANDAU BALLET');
insert into tab_chart values('Albums',to_date('15-05-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');
insert into tab_chart values('Albums',to_date('22-05-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');
insert into tab_chart values('Albums',to_date('29-05-83','dd-mm-yyyy'),'THRILLER','SOME OTHER ARTIST');
insert into tab_chart values('Albums',to_date('05-06-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');
insert into tab_chart values('Albums',to_date('12-06-83','dd-mm-yyyy'),'THRILLER','MICHAEL JACKSON');
insert into tab_chart values('Albums',to_date('19-06-83','dd-mm-yyyy'),'SYNCHRONICITY','THE POLICE');
insert into tab_chart values('Albums',to_date('26-06-83','dd-mm-yyyy'),'SYNCHRONICITY','THE POLICE');
insert into tab_chart values('Albums',to_date('03-07-83','dd-mm-yyyy'),'FANTASTIC','WHAM!');
insert into tab_chart values('Singles',to_date('29-01-60','dd-mm-yyyy'),'STARRY EYED','MICHAEL HOLLIDAY');
insert into tab_chart values('Singles',to_date('05-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');
insert into tab_chart values('Singles',to_date('12-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');
insert into tab_chart values('Singles',to_date('19-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');
insert into tab_chart values('Singles',to_date('26-02-60','dd-mm-yyyy'),'WHY','ANTHONY NEWLEY');
insert into tab_chart values('Singles',to_date('04-03-60','dd-mm-yyyy'),'POOR ME','ADAM FAITH');
insert into tab_chart values('Singles',to_date('10-03-60','dd-mm-yyyy'),'POOR ME','ADAM FAITH');
insert into tab_chart values('Singles',to_date('17-03-60','dd-mm-yyyy'),'RUNNING BEAR','JOHNNY PRESTON');
insert into tab_chart values('Singles',to_date('24-03-60','dd-mm-yyyy'),'RUNNING BEAR','JOHNNY PRESTON');
insert into tab_chart values('Singles',to_date('31-03-60','dd-mm-yyyy'),'MY OLD MAN''S A DUSTMAN','LONNIE DONEGAN');
commit;
select distinct TYPE_OF_CHART, min(DATE_OF_CHART) over(partition by TYPE_OF_CHART,T) DATE_OF_CHART
, TITLE, ARTIST, count(*) over(partition by TYPE_OF_CHART,T) cnt
from (
select TYPE_OF_CHART, DATE_OF_CHART, TITLE, ARTIST, sum(t) over(partition by TYPE_OF_CHART order by DATE_OF_CHART) T
from (
select TYPE_OF_CHART, DATE_OF_CHART, TITLE, ARTIST,
case when TITLE||ARTIST <> nvl(lag(TITLE||ARTIST,1)
over(partition by TYPE_OF_CHART order by DATE_OF_CHART),TITLE||ARTIST) then 1 else 0 end T
from tab_chart))
order by 1,2;
