ORACLE GROUP BY with Date does not group correctly

Question

I try to group a given table by date to get a min and max date of member IDs. The result should display a time range from when to when a member was part of an OE.

my given table (excerpt):

ID  DATE                OE
11  2021-03-06 00:00:00 2926
11  2021-03-07 00:00:00 3879
11  2021-03-08 00:00:00 3879
11  2021-03-09 00:00:00 3879
11  2021-03-10 00:00:00 2926
11  2021-03-11 00:00:00 2926
11  2021-03-12 00:00:00 2926
11  2021-03-13 00:00:00 2926
11  2021-03-14 00:00:00 2926
11  2021-03-15 00:00:00 2926
11  2021-03-16 00:00:00 1344
11  2021-03-17 00:00:00 1344
11  2021-03-18 00:00:00 1344
11  2021-03-19 00:00:00 1344
11  2021-03-20 00:00:00 1344
11  2021-03-21 00:00:00 1344
11  2021-03-22 00:00:00 2926
11  2021-03-23 00:00:00 2926
11  2021-03-24 00:00:00 2926
11  2021-03-25 00:00:00 2926
11  2021-03-26 00:00:00 2926
11  2021-03-27 00:00:00 2926
11  2021-03-28 00:00:00 2926
11  2021-03-29 00:00:00 2926
11  2021-03-30 00:00:00 2926
11  2021-03-31 00:00:00 2926
11  2021-04-01 00:00:00 1549
11  2021-04-02 00:00:00 1549
11  2021-04-03 00:00:00 1549
11  2021-04-04 00:00:00 2926

My Select:

    select id, min(date) as mind, max(date) as maxd,OE
    from <table>
    group by id,oe
    order by mind desc;

The output should be something like this:

ID   | MIND                   | MAXD                   | OE
11     2021-04-04 00:00:00      2021-04-04 00:00:00      2926
11     2021-04-01 00:00:00      2021-04-03 00:00:00      1549
11     2021-03-22 00:00:00      2021-03-31 00:00:00      2926
11     2021-03-16 00:00:00      2021-03-21 00:00:00      1344
11     2021-03-10 00:00:00      2021-03-15 00:00:00      2926
11     2021-03-07 00:00:00      2021-03-09 00:00:00      3879
11     2021-03-06 00:00:00      2021-03-06 00:00:00      2926

But it is like this:

ID   | MIND                   | MAXD                   | OE
11     2021-04-01 00:00:00      2021-04-03 00:00:00      1549
11     2021-03-16 00:00:00      2021-03-21 00:00:00      1344
11     2021-03-07 00:00:00      2021-03-09 00:00:00      3879
11     2021-03-06 00:00:00      2021-04-04 00:00:00      2926

The result should display a time range from when to when a member was part of an OE. Even if I add some other IDs (which I obviously have) it does not show the timeline of OE changes in correct order.

Any help highly appreciated!

TIA,

Michael

Answer 1

You can use a trick called tabibitosan to do this kind of grouping:

SELECT id,
       MIN(dt) AS mind,
       MAX(dt) AS maxd,
       oe
FROM   (SELECT id,
               dt,
               oe,
               row_number() OVER (PARTITION BY ID ORDER BY dt) - row_number() OVER (PARTITION BY ID, oe ORDER BY dt) grp
        FROM   your_table)
GROUP  BY id,
          oe,
          grp
ORDER  BY mind DESC;

See this dbfiddle for results

This works by assigning row numbers across the whole set of data (in this case, it's across each id), and then finding the row numbers across the subsets of data (i.e. across each id and oe), and then subtracting one from the other to form a number you can group by. Consecutive rows get the same group number, but every time there's a non-consecutive row, the group number will change.