- 3,715,597 Users
- 2,242,805 Discussions
- 7,845,453 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 465 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 416 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 8 DevOps
- 3K QA/Testing
- 246 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
How to handle repeated records in SQL?

Hello Community,
I have a simple data set like below:
I need minimum start date & max end date of an employee whenever there is a dept id change or status code change.
So i wrote this formula for Min start date- select emp id, dept id, status code , min(start date) from tablename group by emp id, dept id, status code.
I've got the below result :
The highlighted dates are missing in my result because dept id and status code got repeated. Since i am taking min start date, its not considering those records.
Likewise, i need to get max end date also.
I hope i explained the problem.
Any help would be greatly appreciated.
Thanks.
Update - Adding sample data and expected ouptut.
create table employee(
emp_id int, start_date date, end_date date, dept_id int, status_code varchar(100));
insert into employee values
('2233','1/1/2020','1/25/2020','123','Active'),
('2233','1/25/2020','2/15/2020','123','Active'),
('2233','2/15/2020','3/29/2020','123','Active'),
('2233','3/29/2020','4/11/2020','456','Active'),
('2233','4/11/2020','5/5/2020','456','Leave'),
('2233','5/5/2020','6/17/2020','456','Active'),
('2233','6/17/2020','7/22/2020','456','Active'),
('2233','7/22/2020','8/19/2020','789','Active'),
('2233','8/19/2020','9/30/2020','789','Terminated'),
('2244','1/10/2020','1/25/2020','122','Active'),
('2244','1/25/2020','2/19/2020','122','Leave'),
('2244','2/19/2020','3/31/2020','122','Active'),
('2244','3/31/2020','4/11/2020','322','Active'),
('2244','4/11/2020','5/5/2020','322','Active'),
('2244','5/5/2020','6/17/2020','322','Active'),
('2244','6/17/2020','7/22/2020','322','Active'),
('2244','7/22/2020','8/24/2020','422','Active'),
('2244','8/24/2020','9/24/2020','422','Terminated')
Expected Output:
Best Answer
-
Hi,
Depending on your exact requirements, data and version, you may want something like this:
SELECT * FROM table_x MATCH_RECOGNIZE ( PARTITION BY emp_id ORDER BY start_date MEASURES MIN (start_date) AS min_start_date , MAX (end_date) AS max_end_date ONE ROW PER MATCH PATTERN (x+) DEFINE x AS dept_id = FIRST (dept_id) AND status_code = FIRST (status_code) ) ORDER BY emp_id, min_start_date ;
Of course, I can't test it without any sample data.
Answers
-
Hi,
Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.
What results do you want if end_date on one row is less than start_date of the next row for the same emp_id? What if end_date on one row is later than start_date of the next row? What if two rows have the same emp_id and also the same start_date? If these things are possible in your application, include examples in the sampe data, desired results and explanation.
Always say which version of Oracle you're using (e.g. 12.2.0.1.0).
See: How to Ask Questions in Developer Community Spaces - oracle-tech
-
Hi,
Depending on your exact requirements, data and version, you may want something like this:
SELECT * FROM table_x MATCH_RECOGNIZE ( PARTITION BY emp_id ORDER BY start_date MEASURES MIN (start_date) AS min_start_date , MAX (end_date) AS max_end_date ONE ROW PER MATCH PATTERN (x+) DEFINE x AS dept_id = FIRST (dept_id) AND status_code = FIRST (status_code) ) ORDER BY emp_id, min_start_date ;
Of course, I can't test it without any sample data.
-
Hey Frank,
I've added sample data and expected output.
Some of the keywords - Match_recognize, match are not working in my sql assistant. Can we try this using row number function?
Thanks
-
Hi, @RajeshKota
Please don't change your messages after posting them, especially after other people have already replied; it makes reading the thread harder.
create table employee(
emp_id int, start_date date, end_date date, dept_id int, status_code varchar(100));
insert into employee values
('2233','1/1/2020','1/25/2020','123','Active'),
Don't try to insert VARCHAR2 values (such as '1/1/2020') into DATE columns (like start_date). Use TO_DATE, or DATE literals. Using the wrong data type is simply asking for trouble.
1review the syntax of the INSERT statement. INSERT INTO employee VALUES ... adds only one row. Test (and, if necessary, fix) your statements before you post them.
Some of the keywords - Match_recognize, match are not working in my sql assistant.
Tools are supposed to make things easier for you to do things. Why are you using a tool that makes things harder?