Min and Max values of contiguous rows – Oracle SQL Query

  • Updated
  • Posted in Programming
  • 2 mins read


Q) How to seek out the Minimum and most values of steady sequence numbers in a gaggle of rows.

I do know the issue is just not clear with out giving an instance. Let say I’ve the Employees desk with the beneath information.

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 wish to discover the minimal and most values of steady Emp_Seq numbers. The output ought to 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 question in oracle to seek out the minimal and most values of steady Emp_Seq in every division?

STEP1: First we’ll generate distinctive sequence numbers in every division utilizing the Row_Number analytic operate within the Oracle. The SQL question is.

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

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 worth of rn from emp_seq to establish the continual sequences as a gaggle. The SQL question is

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

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 mixture of the Dept_Id and Dept_Split fields will change into the group for steady rows. Now use group by on these fields and discover the min and max values. The ultimate SQL question 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 staff;
) 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 want this text, then please share it or click on on the google +1 button.

Leave a Reply