Hi,
I am using the MAX OVER partition function but getting odd results
create table test(
id number,
employee_num varchar2(6),
startdate date,
enddate date);
insert into test values(654, 'ABCD' '09-06-2015', '10-07-2017');
insert into test values(657, 'ABCD' '01-03-2014', '11-04-2016');
insert into test values(008, 'ABCD' '01-01-2015', '11-04-2019');
insert into test values(009, 'EFGH' '04-06-2011', '10-07-2016');
insert into test values(657, 'EFGH' '01-03-2009', '11-04-2016');
insert into test values(008, 'EFGH' '01-01-2015', '01-05-2019');
SELECT ID, employee_num, MAX(startdate) OVER(PARTITION BY employee_num) startdate, MAX(enddate) OVER(PARTITION BY employee_num) enddate
FROM TEST
The above query returns all rows
Below is the expected output
| ID | EMPLOYEE_NUM | STARTDATE | ENDDATE |
| 654 | ABCD | 09/06/2015 00:00 | 10/07/2017 00:00 |
| 8 | EFGH | 01/01/2015 00:00 | 01/05/2019 00:00 |