Getting the latest timestamp in Oracle

join;oracletimestamp

I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in master table and the members table. audit_id from the audits table is a foreign key in members table.

I tried to write the query below after researching but I am getting syntax errors.

SELECT
  (SELECT mem.member_id
   FROM members mem
   JOIN audits aud ON mem.audit_id = aud.audit_id
   ORDER BY aud.update_time DESC FETCH FIRST 1 ROWS ONLY) AS latest_id,
       mem1.member_id AS old_id
FROM members mem1
JOIN master ma ON ma.member_id = mem1.member_id

audits

audit_id |update_time 
656566   |2021-01-15 17:01:34
656565   |2020-05-13 17:01:34
545245   |2020-08-11 17:01:34

members

member_id  |audit_id
5655656    |656566
653363     |656565
545454     |545245

master

id | member_id
545634 | 5655656
545634 | 653363
545634 | 545454

Expected output

latest_id | old_id
5655656    |653363
5655656    |545454

Best Answer

With A CTE and ROW_NUMBER in conhunction with Cross JOIN you can get your wanted result.

The CTE creates a temporary table, which has the row number for all all master ids and of course the wanted member id.

The SELECTmakes a Cartesian product of the first (latest) member id and the rest of the rows(member ids) for every master id

CREATE TABLE master
    ("id" int, "member_id" int)
;

INSERT ALL 
    INTO master ("id", "member_id")
         VALUES (545634, 5655656)
    INTO master ("id", "member_id")
         VALUES (545634, 653363)
    INTO master ("id", "member_id")
         VALUES (545634, 545454)
SELECT * FROM dual

CREATE TABLE members
    ("member_id" int, "audit_id" int)
;

INSERT ALL 
    INTO members ("member_id", "audit_id")
         VALUES (5655656, 656566)
    INTO members ("member_id", "audit_id")
         VALUES (653363, 656565)
    INTO members ("member_id", "audit_id")
         VALUES (545454, 545245)
SELECT * FROM dual

CREATE TABLE audits
    ("audit_id" int, "update_time" DATE )
;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

INSERT ALL 
    INTO audits ("audit_id", "update_time")
         VALUES (656566, '2021-01-15 17:01:34')
    INTO audits ("audit_id", "update_time")
         VALUES (656565, '2020-05-13 17:01:34')
    INTO audits ("audit_id", "update_time")
         VALUES (545245, '2020-08-11 17:01:34')
SELECT * FROM dual;

WITH CTE AS (SELECT ma."id",me."member_id",
   Row_NUMBER() OVER (
        PARTITION BY ma."id"
        ORDER BY "update_time" ASC
                ) "rn"
from master ma 
    INNER JOIN members me on ma."member_id" = me."member_id"
    INNER JOIN audits au ON me."audit_id" = au."audit_id")
SELECT a."member_id", b."member_id"
FROM (SELECT "member_id","id" FROM CTE WHERE "rn"= 1) a 
CROSS JOIN (SELECT "member_id","id", "rn" FROM CTE WHERE "rn" > 1)  b 
WHERE a."id" = b."id"
ORDER BY b."rn";

member_id | member_id
--------: | --------:
   653363 |    545454
   653363 |   5655656

db<>fiddle here

Related Question