Getting the latest timestamp in Oracle 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
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
    (&quot;id&quot; int, &quot;member_id&quot; int)
;




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




CREATE TABLE members
    (&quot;member_id&quot; int, &quot;audit_id&quot; int)
;




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




CREATE TABLE audits
    (&quot;audit_id&quot; int, &quot;update_time&quot; DATE )
;




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




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




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


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


db&lt;&gt;fiddle here