11/29/2018 Min and Max values of contiguous rows - Oracle SQL Query
Home Data Warehouse Informatica Informatica Scenarios Informatica Cloud Oracle Unix Hadoop
Search... Search
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
Popular Posts
of rows. Informatica Scenario Based Interview Questions with
Answers - Part 1
I know the problem is not clear without giving an example. Let say I have the Employees table
Unix Sed Command to Delete Lines in File - 15 Examples
with the below data.
String Functions in Hive
Top Examples of Awk Command in Unix
Table Name: Employees
Dept_Id Emp_Seq Sed Command in Unix and Linux Examples
---------------
10 1 SQL Queries Interview Questions - Oracle Part 1
10 2
Date Functions in Hive
10 3
10 5 Design/Implement/Create SCD Type 2 Effective Date
10 6 Mapping in Informatica
10 8
Top Unix Interview Questions - Part 1
10 9
10 11 Grep Command in Unix and Linux Examples
20 1
20 2
Have Questions? Follow Me
I want to find the minimum and maximum values of continuous Emp_Seq numbers. The output
should look as.
https://www.folkstalk.com/2012/03/min-and-max-values-of-contiguous-rows.html 1/5
11/29/2018 Min and Max values of contiguous rows - Oracle SQL Query
vijay bhaskar
Dept_Id Min_Seq Max_Seq
----------------------- Add to circles
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.
994 have me in circles View all
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.
https://www.folkstalk.com/2012/03/min-and-max-values-of-contiguous-rows.html 2/5
11/29/2018 Min and Max values of contiguous rows - Oracle SQL Query
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;
https://www.folkstalk.com/2012/03/min-and-max-values-of-contiguous-rows.html 3/5
11/29/2018 Min and Max values of contiguous rows - Oracle SQL Query
) 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.
1 comment:
Roger Webb 16 July, 2013 09:11
Been looking for a way to do this for awhile. Thank you!
Reply
Enter your comment...
Comment as: Google Accoun
Publish Preview
Newer Post Home Older Post
https://www.folkstalk.com/2012/03/min-and-max-values-of-contiguous-rows.html 4/5
11/29/2018 Min and Max values of contiguous rows - Oracle SQL Query
Subscribe to: Post Comments (Atom)
https://www.folkstalk.com/2012/03/min-and-max-values-of-contiguous-rows.html 5/5
