Hi Tom,
I always learn new tricks from your articles and @ asktom.oracle.com, I have data in table like below.
create table tab1(dat date, price number)
/
insert into tab1 values (to_date('01-01-2020','MM-DD-YYYY'),1000)
/
insert into tab1 values (to_date('01-02-2020','MM-DD-YYYY'),1000)
/
insert into tab1 values (to_date('01-03-2020','MM-DD-YYYY'),2000)
/
insert into tab1 values (to_date('01-04-2020','MM-DD-YYYY'),2000)
/
insert into tab1 values (to_date('01-05-2020','MM-DD-YYYY'),3000)
/
insert into tab1 values (to_date('01-06-2020','MM-DD-YYYY'),2000)
/
I want to see result like below
Mindate maxdate Price
01/01/2020 01/02/2020 1000
01/03/2020 01/04/2020 2000
01/05/2020 01/05/2020 3000
01/06/2020 01/06/2020 2000
Can you please help me forming a SQL query for above result.
Thanks for your help,
Giri
You can see how I build it up bit by bit
SQL>
SQL> create table tab1(dat date, price number)
2 /
Table created.
SQL>
SQL> insert into tab1 values (to_date('01-01-2020','MM-DD-YYYY'),1000)
2 /
1 row created.
SQL> insert into tab1 values (to_date('01-02-2020','MM-DD-YYYY'),1000)
2 /
1 row created.
SQL> insert into tab1 values (to_date('01-03-2020','MM-DD-YYYY'),2000)
2 /
1 row created.
SQL> insert into tab1 values (to_date('01-04-2020','MM-DD-YYYY'),2000)
2 /
1 row created.
SQL> insert into tab1 values (to_date('01-05-2020','MM-DD-YYYY'),3000)
2 /
1 row created.
SQL> insert into tab1 values (to_date('01-06-2020','MM-DD-YYYY'),2000)
2 /
1 row created.
SQL>
SQL> select
2 tab1.*,
3 case when nvl(lag(price) over ( order by dat),-1) != price then
4 row_number() over ( order by dat )
5 end tag
6 from tab1
7 /
DAT PRICE TAG
--------- ---------- ----------
01-JAN-20 1000 1
02-JAN-20 1000
03-JAN-20 2000 3
04-JAN-20 2000
05-JAN-20 3000 5
06-JAN-20 2000 6
6 rows selected.
SQL> select
2 dat, price, max(tag) over ( order by dat ) as tag2
3 from
4 (
5 select
6 tab1.*,
7 case when nvl(lag(price) over ( order by dat),-1) != price then
8 row_number() over ( order by dat )
9 end tag
10 from tab1
11 )
12 /
DAT PRICE TAG2
--------- ---------- ----------
01-JAN-20 1000 1
02-JAN-20 1000 1
03-JAN-20 2000 3
04-JAN-20 2000 3
05-JAN-20 3000 5
06-JAN-20 2000 6
6 rows selected.
SQL> select tag2, min(dat), max(dat)
2 from (
3 select
4 dat, price, max(tag) over ( order by dat ) as tag2
5 from
6 (
7 select
8 tab1.*,
9 case when nvl(lag(price) over ( order by dat),-1) != price then
10 row_number() over ( order by dat )
11 end tag
12 from tab1
13 )
14 )
15 group by tag2
16 order by 1;
TAG2 MIN(DAT) MAX(DAT)
---------- --------- ---------
1 01-JAN-20 02-JAN-20
3 03-JAN-20 04-JAN-20
5 05-JAN-20 05-JAN-20
6 06-JAN-20 06-JAN-20
SQL>
SQL>