Skip to Main Content
  • Questions
  • Grouping same value in different groups

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: March 25, 2020 - 5:43 pm UTC

Answered by: Chris Saxon - Last updated: March 26, 2020 - 4:36 pm UTC

Category: SQL - Version: Oracle Database 12c Release 12.2.0.1.0

Viewed 100+ times

You Asked

Database:
Oracle Database 12c Release 12.2.0.1.0

Following is my test case script:

create table test
(
       id             number(1),
       sdate          date,
       tdate          date,
       prnt_id        number(1)
);

insert into test (id, sdate, tdate, prnt_id) values (1, to_date('10/17/2012','mm/dd/yyyy'), to_date('10/16/2014','mm/dd/yyyy'), 2);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('10/16/2014','mm/dd/yyyy'), to_date('2/16/2016','mm/dd/yyyy'), 2);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('2/16/2016','mm/dd/yyyy'), to_date('9/30/2016','mm/dd/yyyy'), 3);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('9/30/2016','mm/dd/yyyy'), to_date('3/16/2017','mm/dd/yyyy'), 3);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('3/16/2017','mm/dd/yyyy'), to_date('1/16/2019','mm/dd/yyyy'), 2);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('1/16/2019','mm/dd/yyyy'), to_date('10/16/2019','mm/dd/yyyy'), 2);
insert into test (id, sdate, tdate, prnt_id) values (1, to_date('10/16/2019','mm/dd/yyyy'), to_date('12/1/2999','mm/dd/yyyy'), 2);

commit;

select * from test order by sdate;



Question:

I want to modify the above Select SQL which returns all 7 rows from test table, selects all the columns plus two more columns.
First additional column (min_sdate) will return 10/17/2012 for rows 1,2 and 2/16/2016 for rows 3,4 and 3/16/2017 for rows 5,6,7.
Second additional column (max_tdate) will return 2/16/2016 for rows 1,2 and 3/16/2017 for rows 3,4 and 12/1/2999 for rows 5,6,7.
Basically, I'm trying to group by prnt_id column but instead of two groups (prnt_id: 2 and 3), I want three groups (prnt_id: 2,3,2), and then for those three groups get the min(sdate) and max(tdate).
I was thinking I could use analytical function min() and max() with window clause to achieve this, but not sure how to frame the SQL.

Any or all help will be appreciated. Thanks!

and we said...

I was thinking I could use analytical function min() and max() with window clause to achieve this

Indeed you can. But there's another way:

Pattern matching!

With this you use match_recognize to:

- Partition by ID
- Sort by date
- Find any row (init; undefined means "always true")
- Then keep adding rows to the group if they have the same PRNT_ID value as the previous ( prnt_id = prev ( prnt_id ) and pattern same_val*)
- Return all the rows
- Add the min start dt and final max end date to the results in the measures clause

Which gives:

alter session set nls_date_format = 'DD MON YYYY';

select id, sdate, tdate, prnt_id, mn, mx
from   test match_recognize (
  partition by id
  order by sdate
  measures 
    min ( sdate ) as mn,
    final max ( tdate ) as mx
  all rows per match
  pattern ( init same_val* )
  define 
    same_val as prnt_id = prev ( prnt_id )
);

ID    SDATE          TDATE          PRNT_ID    MN             MX              
    1 17 OCT 2012    16 OCT 2014             2 17 OCT 2012    16 FEB 2016    
    1 16 OCT 2014    16 FEB 2016             2 17 OCT 2012    16 FEB 2016    
    1 16 FEB 2016    30 SEP 2016             3 16 FEB 2016    16 MAR 2017    
    1 30 SEP 2016    16 MAR 2017             3 16 FEB 2016    16 MAR 2017    
    1 16 MAR 2017    16 JAN 2019             2 16 MAR 2017    01 DEC 2999    
    1 16 JAN 2019    16 OCT 2019             2 16 MAR 2017    01 DEC 2999    
    1 16 OCT 2019    01 DEC 2999             2 16 MAR 2017    01 DEC 2999  

and you rated our response

  (1 rating)

Reviews

Awesome!

March 26, 2020 - 3:47 pm UTC

Reviewer: A reader

This is great. I've never used pattern matching with measures and pattern. I got to learn something new. Thank you!
Chris Saxon

Followup  

March 26, 2020 - 4:36 pm UTC

Great :)

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.