Retrieve data in group based on the Value of other column

Question

I have one table with the following Column.

BoxNumber          Status
580                 4 
581                 4
582                 4
583                 4
584                 2
585                 2
586                 4
587                 4
588                 4
589                 4
590                 2
591                 2

I need one select Query to get the following output .

StartingBoxNumber EndingBoxNumber    Status
580                     583           4
584                     585           2
586                     589           4
590                     591           2

Answer 1

Assuming box numbers are always consecutive:

SELECT  COALESCE(
        LAG(Boxnumber) OVER (ORDER BY BoxNumber),
        (
        SELECT  MIN(BoxNumber)
        FROM    mytable
        )) AS StartBoxNumber,
        BoxNumber AS EndBoxNumber,
        status
FROM    mytable qo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable  qi
        WHERE   qi.boxnumber = qo.boxnumber + 1
                AND qi.status = qo.status
        )

Answer 2

Hi Seema,

You can get the result with a single scan of the table, using analytics to define "groups" of contiguous rows:

SQL> SELECT MIN(boxnumber), MAX(boxnumber), status
  2    FROM (SELECT boxnumber, status,
  3                 SUM(status_change) over(ORDER BY boxnumber) group_id
  4             FROM (SELECT boxnumber, status,
  5                           CASE
  6                              WHEN lag(status) over(ORDER BY boxnumber)
  7                                   = status
  8                               AND lag(boxnumber) over(ORDER BY boxnumber)
  9                                   = boxnumber - 1 THEN
 10                               0
 11                              ELSE
 12                               1
 13                           END status_change
 14                      FROM box))
 15   GROUP BY status, group_id
 16   ORDER BY 1;

MIN(BOXNUMBER) MAX(BOXNUMBER)     STATUS
-------------- -------------- ----------
           580            583          4
           584            585          2
           586            589          4
           590            591          2