Min and Max values of contiguous rows - Oracle SQL Query

Q) How to find the Minimum and maximum values of continuous sequence numbers in a group of rows.

I know the problem is not clear without giving an example. Let say I have the Employees table with the below data.

Table Name: Employees
Dept_Id Emp_Seq
---------------
10       1
10       2
10       3
10       5
10       6
10       8
10       9
10       11
20       1
20       2

I want to find the minimum and maximum values of continuous Emp_Seq numbers. The output should look as.

Dept_Id Min_Seq Max_Seq
-----------------------
10      1       3
10      5       6
10      8       9
10      11      11
20      1       2

Write an SQL query in oracle to find the minimum and maximum values of continuous Emp_Seq in each department?

STEP1: First we will generate unique sequence numbers in each department using the Row_Number analytic function in the Oracle. The SQL query is.

SELECT  Dept_Id,
        Emp_Seq,
 ROW_NUMBER() OVER (PARTITION BY Dept_Id ORDER BY Emp_Seq) rn
FROM    employees;

Dept_Id Emp_Seq  rn
--------------------
10       1       1
10       2       2
10       3       3
10       5       4
10       6       5
10       8       6
10       9       7
10       11      8
20       1       1
20       2       2

STEP2: Subtract the value of rn from emp_seq to identify the continuous sequences as a group. The SQL query is

SELECT  Dept_Id,
        Emp_Seq,
 Emp_Seq-ROW_NUMBER() OVER (PARTITION BY Dept_Id ORDER BY Emp_Seq) Dept_Split
FROM    employees;

Dept_Id Emp_Seq  Dept_Split
---------------------------
10       1       0
10       2       0
10       3       0
10       5       1
10       6       1
10       8       2
10       9       2
10       11      3
20       1       0
20       2       0

STEP3: The combination of the Dept_Id and Dept_Split fields will become the group for continuous rows. Now use group by on these fields and find the min and max values. The final SQL query is

SELECT  Dept_Id,
        MIN(Emp_Seq) Min_Seq,
        MAX(Emp_Seq) Max_Seq
FROM
(
SELECT  Dept_Id,
        Emp_Seq,
 Emp_Seq-ROW_NUMBER() OVER (PARTITION BY Dept_Id ORDER BY Emp_Seq) Dept_Split
FROM    employees;
) A
Group BY Dept_Id, Dept_Split

Recommended Reading:

---

Oracle Query to split the delimited data in a column to multiple rows
How to find median using oracle sql query
Oracle Complex Queries - Part 3
Oracle Analytical Functions Part 1
SQL Interview Questions and Answers

If you like this article, then please share it or click on the google +1 button.