# 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: EmployeesDept_Id Emp_Seq---------------10       110       210       310       510       610       810       910       1120       120       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       310      5       610      8       910      11      1120      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) rnFROM    staff;Dept_Id Emp_Seq  rn--------------------10       1       110       2       210       3       310       5       410       6       510       8       610       9       710       11      820       1       120       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_SplitFROM    staff;Dept_Id Emp_Seq  Dept_Split---------------------------10       1       010       2       010       3       010       5       110       6       110       8       210       9       210       11      320       1       020       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_SeqFROM(SELECT  Dept_Id,Emp_Seq,Emp_Seq-ROW_NUMBER() OVER (PARTITION BY Dept_Id ORDER BY Emp_Seq) Dept_SplitFROM    staff;) AGroup BY Dept_Id, Dept_Split`