PG version 15.4
create table notes(noteoid numeric(10),
userid varchar(100),
note varchar(100),
modifydate timestamp);
create table notesxref(entityoid numeric(10) ,
noteoid numeric(10),
notedate timestamp);
create table timepair(objectid numeric(10) ,
intime timestamp,
outtime timestamp);
insert
into
timepair (objectid,
intime,
outtime)
values(21967046,
'2025-01-28 12:00:00',
'2025-01-28 15:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967047,
'2025-01-28 17:00:00',
'2025-01-28 19:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967065,
'2025-01-29 08:00:00',
'2025-01-29 12:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21966965,
'2025-02-02 09:00:00',
'2025-02-02 12:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21966985,
'2025-02-02 14:00:00',
'2025-02-02 17:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967025,
'2025-02-03 08:00:00',
'2025-02-03 10:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967027,
'2025-02-03 16:00:00',
'2025-02-03 18:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967006,
'2025-02-04 13:00:00',
'2025-02-04 15:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967007,
'2025-02-04 16:00:00',
'2025-02-04 18:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21953965,
'2025-01-13 01:00:00',
'2025-01-13 18:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967045,
'2025-01-13 09:00:00',
'2025-01-13 10:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967026,
'2025-01-13 12:00:00',
'2025-01-13 14:00:00');
insert
into
timepair (objectid,
intime,
outtime)
values(21967005,
'2025-01-13 09:00:00',
'2025-01-13 12:00:00');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369388,
'ID40247137',
'entry-2',
'2025-02-10 12:20:17');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369387,
'ID40247137',
'entry-1',
'2025-02-10 12:20:17');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369338,
'ID40247137',
'comment-latest-2',
'2025-02-10 12:18:15');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369337,
'ID40247137',
'comment-latest-1',
'2025-02-10 12:18:15');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369237,
'ID40247137',
'timepair-1 - comment-2',
'2025-02-10 12:04:10');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369139,
'ID40247137',
'Timepair-3',
'2025-02-10 12:00:36');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369138,
'ID40247137',
'Timepair-2',
'2025-02-10 12:00:36');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369137,
'ID40247137',
'Timepair-1',
'2025-02-10 12:00:36');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40369087,
'ID40247137',
'comment-4',
'2025-02-10 11:56:07');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368989,
'ID40247137',
'comment-3',
'2025-02-10 11:48:28');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368988,
'ID40247137',
'comment-2',
'2025-02-10 11:48:28');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368987,
'ID40247137',
'comment-1',
'2025-02-10 11:48:28');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368488,
'ID40247137',
'comment-14',
'2025-02-10 10:45:49');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368487,
'ID40247137',
'comment-13',
'2025-02-10 10:45:49');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368437,
'ID40247137',
'comment-12',
'2025-02-10 10:43:56');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368387,
'ID40247137',
'comment-11',
'2025-02-10 10:42:43');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368337,
'ID40247137',
'comment-10',
'2025-02-10 10:41:23');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368239,
'ID40247137',
'comment-9',
'2025-02-10 10:38:56');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368238,
'ID40247137',
'comment-8',
'2025-02-10 10:38:56');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368237,
'ID40247137',
'comment-7',
'2025-02-10 10:38:56');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368138,
'ID40247137',
'comment-6',
'2025-02-10 10:34:21');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368137,
'ID40247137',
'comment-5',
'2025-02-10 10:34:21');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368087,
'ID40247137',
'comment-4',
'2025-02-10 10:33:16');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368039,
'ID40247137',
'comment-3',
'2025-02-10 10:31:58');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368038,
'ID40247137',
'comment-2',
'2025-02-10 10:31:58');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40368037,
'ID40247137',
'comment-1',
'2025-02-10 10:31:58');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367937,
'ID40247137',
'comment-14 for 2pm',
'2025-02-10 10:29:49');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367887,
'ID40247137',
'comment-13',
'2025-02-10 10:29:07');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367838,
'ID40247137',
'comment-12 for 2pm',
'2025-02-10 10:28:02');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367837,
'ID40247137',
'comment-11',
'2025-02-10 10:28:02');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367787,
'ID40247137',
'comment-10',
'2025-02-10 10:26:56');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367687,
'ID40247137',
'comment-8',
'2025-02-10 10:25:07');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367638,
'ID40247137',
'comment-7 for 2pm',
'2025-02-10 10:23:58');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367637,
'ID40247137',
'comment-6',
'2025-02-10 10:23:58');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367588,
'ID40247137',
'comment-5 for 2pm',
'2025-02-10 10:22:16');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367587,
'ID40247137',
'comment-4',
'2025-02-10 10:22:16');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367487,
'ID40247137',
'comment-3 for 2pm',
'2025-02-10 10:20:39');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367437,
'ID40247137',
'comment-2',
'2025-02-10 10:19:34');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40367387,
'ID40247137',
'Comment-1',
'2025-02-10 10:17:18');
insert
into
notes (noteoid,
userid,
note,
modifydate)
values(40291287,
'ID40247137',
'comment',
'2025-02-06 10:33:34');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967065',
40369388,
'2025-02-10 12:20:17');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967065',
40369387,
'2025-02-10 12:20:17');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967046',
40369338,
'2025-02-10 12:18:15');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967045',
40369337,
'2025-02-10 12:18:15');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967045',
40369237,
'2025-02-10 12:04:10');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967047',
40369139,
'2025-02-10 12:00:36');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967046',
40369138,
'2025-02-10 12:00:36');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967045',
40369137,
'2025-02-10 12:00:36');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967025',
40369087,
'2025-02-10 11:56:07');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967027',
40368989,
'2025-02-10 11:48:28');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967026',
40368988,
'2025-02-10 11:48:28');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967025',
40368987,
'2025-02-10 11:48:28');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967006',
40368488,
'2025-02-10 10:45:49');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967005',
40368487,
'2025-02-10 10:45:49');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967006',
40368437,
'2025-02-10 10:43:56');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967007',
40368387,
'2025-02-10 10:42:43');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967005',
40368337,
'2025-02-10 10:41:23');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967007',
40368239,
'2025-02-10 10:38:56');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967006',
40368238,
'2025-02-10 10:38:56');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967005',
40368237,
'2025-02-10 10:38:56');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967006',
40368138,
'2025-02-10 10:34:21');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967006',
40368137,
'2025-02-10 10:34:21');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967005',
40368087,
'2025-02-10 10:33:16');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967007',
40368039,
'2025-02-10 10:31:58');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967006',
40368038,
'2025-02-10 10:31:58');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21967005',
40368037,
'2025-02-10 10:31:58');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966985',
40367937,
'2025-02-10 10:29:49');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966965',
40367887,
'2025-02-10 10:29:07');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966985',
40367838,
'2025-02-10 10:28:02');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966965',
40367837,
'2025-02-10 10:28:02');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966965',
40367787,
'2025-02-10 10:26:56');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966965',
40367687,
'2025-02-10 10:25:07');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966985',
40367638,
'2025-02-10 10:23:58');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966965',
40367637,
'2025-02-10 10:23:58');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966985',
40367588,
'2025-02-10 10:22:16');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966965',
40367587,
'2025-02-10 10:22:16');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966985',
40367487,
'2025-02-10 10:20:39');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966965',
40367437,
'2025-02-10 10:19:34');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21966965',
40367387,
'2025-02-10 10:17:18');
insert
into
notesxref (entityoid,
noteoid,
notedate)
values('21953965',
40291287,
'2025-02-06 10:33:34');
Here is the query to get the latest note(comment) if they have a certain timepair (both date and time).
For example, note entry-1 and entry-2 have the same timepairs, but entry-2 was latest based on the noteoid and modifydate.
And comment-latest-1 and comment-latest-2, having different timepairs on the same date, but with a different time (timepair.INTIME and timepair.OUTTIME), but it gets 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 it is the same with comment-1 and comment-2, which have different timepairs on the same date but a different time (timepair.INTIME and timepair.OUTTIME), but it getsfiltered 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)