Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

To get the latest comment

cubeguyFeb 10 2025
create table notes(noteoid number,userid varchar2(100), note varchar2(100), modifydate date);
create table notesxref(entityoid number ,noteoid number,notedate date);
create table timepair(objectid number ,intime date,outtime date);

INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369388, 'ID40247137', 'entry-2', to_date('2025-02-10 12:20:17','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369387, 'ID40247137', 'entry-1', to_date('2025-02-10 12:20:17','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369338, 'ID40247137', 'comment-latest-2', to_date('2025-02-10 12:18:15','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369337, 'ID40247137', 'comment-latest-1', to_date('2025-02-10 12:18:15','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369237, 'ID40247137', 'timepair-1 - comment-2', to_date('2025-02-10 12:04:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369139, 'ID40247137', 'Timepair-3', to_date('2025-02-10 12:00:36','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369138, 'ID40247137', 'Timepair-2', to_date('2025-02-10 12:00:36','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369137, 'ID40247137', 'Timepair-1', to_date('2025-02-10 12:00:36','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40369087, 'ID40247137', 'comment-4', to_date('2025-02-10 11:56:07','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368989, 'ID40247137', 'comment-3', to_date('2025-02-10 11:48:28','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368988, 'ID40247137', 'comment-2', to_date('2025-02-10 11:48:28','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368987, 'ID40247137', 'comment-1', to_date('2025-02-10 11:48:28','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368488, 'ID40247137', 'comment-14', to_date('2025-02-10 10:45:49','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368487, 'ID40247137', 'comment-13', to_date('2025-02-10 10:45:49','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368437, 'ID40247137', 'comment-12', to_date('2025-02-10 10:43:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368387, 'ID40247137', 'comment-11', to_date('2025-02-10 10:42:43','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368337, 'ID40247137', 'comment-10', to_date('2025-02-10 10:41:23','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368239, 'ID40247137', 'comment-9', to_date('2025-02-10 10:38:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368238, 'ID40247137', 'comment-8', to_date('2025-02-10 10:38:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368237, 'ID40247137', 'comment-7', to_date('2025-02-10 10:38:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368138, 'ID40247137', 'comment-6', to_date('2025-02-10 10:34:21','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368137, 'ID40247137', 'comment-5', to_date('2025-02-10 10:34:21','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368087, 'ID40247137', 'comment-4', to_date('2025-02-10 10:33:16','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368039, 'ID40247137', 'comment-3', to_date('2025-02-10 10:31:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368038, 'ID40247137', 'comment-2', to_date('2025-02-10 10:31:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40368037, 'ID40247137', 'comment-1', to_date('2025-02-10 10:31:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367937, 'ID40247137', 'comment-14 for 2pm', to_date('2025-02-10 10:29:49','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367887, 'ID40247137', 'comment-13', to_date('2025-02-10 10:29:07','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367838, 'ID40247137', 'comment-12 for 2pm', to_date('2025-02-10 10:28:02','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367837, 'ID40247137', 'comment-11', to_date('2025-02-10 10:28:02','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367787, 'ID40247137', 'comment-10', to_date('2025-02-10 10:26:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367687, 'ID40247137', 'comment-8', to_date('2025-02-10 10:25:07','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367638, 'ID40247137', 'comment-7 for 2pm', to_date('2025-02-10 10:23:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367637, 'ID40247137', 'comment-6', to_date('2025-02-10 10:23:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367588, 'ID40247137', 'comment-5 for 2pm', to_date('2025-02-10 10:22:16','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367587, 'ID40247137', 'comment-4', to_date('2025-02-10 10:22:16','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367487, 'ID40247137', 'comment-3 for 2pm', to_date('2025-02-10 10:20:39','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367437, 'ID40247137', 'comment-2', to_date('2025-02-10 10:19:34','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40367387, 'ID40247137', 'Comment-1', to_date('2025-02-10 10:17:18','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notes
(noteoid, userid, note, modifydate)
VALUES(40291287, 'ID40247137', 'comment', to_date('2025-02-06 10:33:34','YYYY-MM-DD HH24:MI:SS'));


INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967065', 40369388, to_date('2025-02-10 12:20:17','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967065', 40369387, to_date('2025-02-10 12:20:17','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967046', 40369338, to_date('2025-02-10 12:18:15','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967045', 40369337, to_date('2025-02-10 12:18:15','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967045', 40369237, to_date('2025-02-10 12:04:10','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967047', 40369139, to_date('2025-02-10 12:00:36','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967046', 40369138, to_date('2025-02-10 12:00:36','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967045', 40369137, to_date('2025-02-10 12:00:36','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967025', 40369087, to_date('2025-02-10 11:56:07','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967027', 40368989, to_date('2025-02-10 11:48:28','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967026', 40368988, to_date('2025-02-10 11:48:28','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967025', 40368987, to_date('2025-02-10 11:48:28','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967006', 40368488, to_date('2025-02-10 10:45:49','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967005', 40368487, to_date('2025-02-10 10:45:49','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967006', 40368437, to_date('2025-02-10 10:43:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967007', 40368387, to_date('2025-02-10 10:42:43','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967005', 40368337, to_date('2025-02-10 10:41:23','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967007', 40368239, to_date('2025-02-10 10:38:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967006', 40368238, to_date('2025-02-10 10:38:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967005', 40368237, to_date('2025-02-10 10:38:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967006', 40368138, to_date('2025-02-10 10:34:21','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967006', 40368137, to_date('2025-02-10 10:34:21','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967005', 40368087, to_date('2025-02-10 10:33:16','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967007', 40368039, to_date('2025-02-10 10:31:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967006', 40368038, to_date('2025-02-10 10:31:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21967005', 40368037, to_date('2025-02-10 10:31:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966985', 40367937, to_date('2025-02-10 10:29:49','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966965', 40367887, to_date('2025-02-10 10:29:07','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966985', 40367838, to_date('2025-02-10 10:28:02','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966965', 40367837, to_date('2025-02-10 10:28:02','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966965', 40367787, to_date('2025-02-10 10:26:56','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966965', 40367687, to_date('2025-02-10 10:25:07','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966985', 40367638, to_date('2025-02-10 10:23:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966965', 40367637, to_date('2025-02-10 10:23:58','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966985', 40367588, to_date('2025-02-10 10:22:16','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966965', 40367587, to_date('2025-02-10 10:22:16','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966985', 40367487, to_date('2025-02-10 10:20:39','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966965', 40367437, to_date('2025-02-10 10:19:34','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21966965', 40367387, to_date('2025-02-10 10:17:18','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO notesxref
(entityoid, noteoid, notedate)
VALUES('21953965', 40291287, to_date('2025-02-06 10:33:34','YYYY-MM-DD HH24:MI:SS'));


INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967046, to_date('2025-01-28 12:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-01-28 15:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967047, to_date('2025-01-28 17:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-01-28 19:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967065, to_date('2025-01-29 08:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-01-29 12:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21966965, to_date('2025-02-02 09:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-02-02 12:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21966985, to_date('2025-02-02 14:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-02-02 17:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967025, to_date('2025-02-03 08:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-02-03 10:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967027, to_date('2025-02-03 16:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-02-03 18:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967006, to_date('2025-02-04 13:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-02-04 15:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967007, to_date('2025-02-04 16:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-02-04 18:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21953965, to_date('2025-01-13 01:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-01-13 18:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967045, to_date('2025-01-13 09:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-01-13 10:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967026, to_date('2025-01-13 12:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-01-13 14:00:00','YYYY-MM-DD HH24:MI:SS'));
INSERT INTO timepair
(objectid, intime, outtime)
VALUES(21967005, to_date('2025-01-13 09:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2025-01-13 12:00:00','YYYY-MM-DD HH24:MI:SS'));


Here is the query to get the latest note(comment) if they have timepair
for example note entry-1 and entry-2 have same timepairs(both date and time) but entry-2 was latest based on the noteoid and modifydate.
But comment-latest-1 and comment-latest-2, having different timepairs on same date but different time(timepair.INTIME and timepair.OUTTIME), but it getting filtered out by "and not EXISTS (SELECT 1 FROM notes later_nt WHERE later_nt.modifydate = nt.modifydate AND later_nt.noteoid > nt.noteoid)"
and same with comment-2 . i.e comment-1 and comment-2 , having different timepairs on same date but different time(timepair.INTIME and timepair.OUTTIME), but it getting filtered out by "and not EXISTS (SELECT 1 FROM notes later_nt WHERE later_nt.modifydate = nt.modifydate AND later_nt.noteoid > nt.noteoid)"

How to get these two?


with xref2 as 
(SELECT ref.entityoid,nt.userid,nt.note,ref.notedate,nt.modifydate,nt.noteoid 
FROM notesxref ref JOIN notes nt ON ref.noteoid = nt.noteoid 
where userid = 'ID40247137' 
and not EXISTS (SELECT 1 FROM notesxref later_ref WHERE later_ref.entityoid = ref.entityoid AND later_ref.notedate > ref.notedate) 
and not EXISTS (SELECT 1 FROM notes later_nt WHERE later_nt.modifydate = nt.modifydate AND later_nt.noteoid > nt.noteoid) 
) select tp.intime,tp.outtime,note from timepair tp join xref2 on( tp.objectid = xref2.entityoid) 

Please note that above is the part of bigger SQL.
Comments
Post Details
Added on Feb 10 2025
#sql
1 comment
102 views