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

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 select开发者_Go百科ing 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

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

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

Though has already been answered, wanted to share it. It uses JOINs instead

WITH DATA AS (
SELECT '1/01/2000' mydate, 1 GroupID, 44 v
UNION ALL SELECT '2/01/2000', 1, 55 
UNION ALL SELECT '3/01/2000', 1, 66 
UNION ALL SELECT '4/01/2000', 2, 77 
UNION ALL SELECT '5/01/2000', 2, 88 
UNION ALL SELECT '6/01/2000', 1, 99 
UNION ALL SELECT '7/01/2000', 1, 22 
) 

SELECT 
  a.GroupID,
  a.mydate
FROM 
  DATA a 
  JOIN DATA b ON ( b.mydate <= a.mydate )
GROUP BY 
  a.GroupID,
  a.mydate
HAVING 
  MAX( b.GroupID ) = MIN ( b.GroupID )
ORDER BY 
  a.mydate