Grouping SQL results using contiguous time intervals (oracle sql)

I have the following data in a table as shown below and I am looking for a way to group contiguous time intervals for each returned id:

CREATE TABLE DUMMY
(
  ID          VARCHAR2(10 BYTE),
  TIME_STAMP  VARCHAR2(8 BYTE),
  NAME        VARCHAR2(255 BYTE)
);

SELECT ID, min(TIME_STAMP) "startDate", max(TIME_STAMP) "endDate", NAME
GROUP BY ID , NAME

      

        
        
        
      

    

sort of

100 20011128 20011203 David
100 20011204 20011207 Unknown
100 20011208 20011215 David
100 20011216 20011220 Sara

      

        
        
        
      

    

etc.

ps. I have a sample script, but I don't know how to attach my file.

Hello everyone here's more typing:

• There is only one entry for a specific identifier with the time_stamp parameter.
• Users can be different, for example, for the first day of David, the 2nd day of the unknown, the 3rd day of David, etc.

Thus, for each ID, there is one row for each day of the year, but with different users. Now, I want to see the breakpoint, the base difference at the time_stamp intervals from the first day to the last day for a specific ID in the daily order from the start of the day to the last day.

The query result should be:

ID   NAME     MIN_DATE  MAX_DATE
100  David    20011128  20050407
100  Sara     20050408  20050417
100  David    20050418  20080416
100  Unknown  20080417  20080507
100  David    20080508  20080508
100  Unknown  20080509  20080607
100  David    20080608  20080608
100  Unknown  20080609  20080921
100  David    20080922  20080922
100  Unknown  20080923  20081231
100  David    20090101  20090405

      

        
        
        
      

    

thanks

Hi, thanks a lot everyone, I solved the problem, here is the solution:

select id, min(time_stamp), max(time_stamp), name
from   ( select id, time_stamp, name,
                max(rn) over (order by time_stamp) grp
         from   ( select id, time_stamp, name,
                         case
                              when lag(name) over (order by time_stamp) <> name or
                                   row_number() over (order by time_stamp) = 1
                              then row_number() over (order by time_stamp)
                         end rn
                  from   dummy
                )
       )
group by id, grp, name
order by 1