How to select the first continous group of rows using Oracle SQL

Question

Hi All,

I have the following data:

Date             GroupID     Value
1/01/2000    1                 44
2/01/2000    1                 55
3/01/2000    1                 66
4/01/2000    2                 77
5/01/2000    2                 88
6/01/2000    1                 99
7/01/2000    1                 22

I am looking for a query capable of selecting the first group of records with a continous GroupID when I order by Date. i.e. in this example I would get:

1/01/2000    1                 44
2/01/2000    1                 55
3/01/2000    1                 66

As the group ID changed in the next row, I wouldn't get any subsequent data.

Help will be greatly appreciated

Thanks

Elie

Answer 1

Hi Elie,

one way to do it:

SQL> WITH DATA AS (
  2  SELECT '1/01/2000' mydate, 1 GroupID, 44 Value FROM DUAL
  3  UNION ALL SELECT '2/01/2000', 1, 55 FROM DUAL
  4  UNION ALL SELECT '3/01/2000', 1, 66 FROM DUAL
  5  UNION ALL SELECT '4/01/2000', 2, 77 FROM DUAL
  6  UNION ALL SELECT '5/01/2000', 2, 88 FROM DUAL
  7  UNION ALL SELECT '6/01/2000', 1, 99 FROM DUAL
  8  UNION ALL SELECT '7/01/2000', 1, 22 FROM DUAL
  9  )
 10  SELECT mydate, groupid, VALUE
 11    FROM (SELECT mydate, groupid, VALUE,
 12                 SUM(gap) over(ORDER BY mydate) contiguous_group
 13             FROM (SELECT mydate, groupid, VALUE,
 14                           CASE
 15                              WHEN lag(groupid)
 16                                   over(ORDER BY mydate) != groupid
 17                              THEN
 18                               1
 19                              ELSE
 20                               0
 21                           END gap
 22                      FROM DATA))
 23   WHERE contiguous_group = 0;

MYDATE       GROUPID      VALUE
--------- ---------- ----------
1/01/2000          1         44
2/01/2000          1         55
3/01/2000          1         66

Answer 2

Hi All,

This is another way how to do it

    WITH data AS
 (SELECT '1/01/2000' mydate,
         1 groupid,
         44 VALUE
    FROM dual
  UNION ALL
  SELECT '2/01/2000',
         1,
         55
    FROM dual
  UNION ALL
  SELECT '3/01/2000',
         1,
         66
    FROM dual
  UNION ALL
  SELECT '4/01/2000',
         2,
         77
    FROM dual
  UNION ALL
  SELECT '5/01/2000',
         2,
         88
    FROM dual
  UNION ALL
  SELECT '6/01/2000',
         1,
         99
    FROM dual
  UNION ALL
  SELECT '7/01/2000',
         1,
         22
    FROM dual)
SELECT *
  FROM data
 WHERE rownum <= (SELECT MAX(rwn)
                    FROM (SELECT COUNT(*) over(PARTITION BY groupid ORDER BY rownum) cnt,
                                 rownum rwn
                            FROM data)
                   WHERE rwn = cnt);

P.S. query from Vincent will not work if all mydate values will be same