By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE COURSE
("STUDENT_ID" int, "COURSE_ID" int, "COURSE_NAME" varchar2(31), "COURSE_START_DATE" timestamp);
INSERT ALL
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100001, -100, 'C Programming Language', '04-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100001, -200, 'Java Programming Language', '11-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100001, -300, 'C# Programming Language', '07-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100001, 100, 'Data Structure and algorithms', '05-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100001, 200, 'Computer Graphics', '13-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100001, 300, 'Networking', '02-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100002, -300, 'C# Programming Language', '12-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100002, -400, 'Python Programming Language', '07-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100002, -500, 'JavaScript Programming Language', '08-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100002, 100, 'Data Structure and algorithms', '17-Jan-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100002, 300, 'Computer Graphics', '26-Jan-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100002, 400, 'DataBase Management', '10-Jan-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100003, -500, 'JavaScript Programming Language', '07-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100003, -600, 'SQL', '13-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100003, -200, 'Java Programming Language', '17-Jan-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100003, 300, 'Networking', '04-Feb-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
VALUES (100003, 400, 'DataBase Management', '05-Jan-2019 12:00:00 AM')
INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
18 rows affected
SELECT
STUDENT_ID,
COURSE_ID,
COURSE_NAME,
COURSE_START_DATE
FROM COURSE t
WHERE COURSE_ID != 0
AND NOT EXISTS
(
SELECT 1
FROM COURSE d
WHERE d.STUDENT_ID = t.STUDENT_ID
AND d.COURSE_START_DATE > t.COURSE_START_DATE
AND SIGN(d.COURSE_ID) = SIGN(t.COURSE_ID)
AND d.COURSE_ID != 0
)
ORDER BY SIGN(COURSE_ID), STUDENT_ID
| STUDENT_ID | COURSE_ID | COURSE_NAME | COURSE_START_DATE |
|---|---|---|---|
| 100001 | -200 | Java Programming Language | 11-FEB-19 12.00.00.000000 AM |
| 100002 | -300 | C# Programming Language | 12-FEB-19 12.00.00.000000 AM |
| 100003 | -600 | SQL | 13-FEB-19 12.00.00.000000 AM |
| 100001 | 200 | Computer Graphics | 13-FEB-19 12.00.00.000000 AM |
| 100002 | 300 | Computer Graphics | 26-JAN-19 12.00.00.000000 AM |
| 100003 | 300 | Networking | 04-FEB-19 12.00.00.000000 AM |