Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE sts_tbl (Emplid, REQUEST_ID, Status, Status_Dttm) AS
SELECT 1234, 1, 'Open',        DATE '2022-06-02' + INTERVAL '35' MINUTE FROM DUAL UNION ALL
SELECT 1231, 5, 'Draft',       DATE '2022-06-02' + INTERVAL '30' MINUTE FROM DUAL UNION ALL
SELECT 1234, 1, 'In Progress', DATE '2022-06-02' + INTERVAL '47' MINUTE FROM DUAL;
3 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT MIN(emplid) AS emplid,
       request_id,
       MIN(status_dttm) AS request_date,
       MAX(status) KEEP (DENSE_RANK LAST ORDER BY status_dttm) AS current_status 
FROM   sts_tbl
GROUP BY request_id


EMPLID
REQUEST_ID
REQUEST_DATE
CURRENT_STATUS


1234
1
2022-06-02 00:35:00
In Progress

1231
5
2022-06-02 00:30:00
Draft


SELECT emplid,
       MIN(status_dttm) AS request_date,
       MAX(status) KEEP (DENSE_RANK LAST ORDER BY status_dttm) AS current_status 
FROM   sts_tbl
GROUP BY emplid


EMPLID
REQUEST_DATE
CURRENT_STATUS


1231
2022-06-02 00:30:00
Draft

1234
2022-06-02 00:35:00
In Progress

EMPLID	REQUEST_ID	REQUEST_DATE	CURRENT_STATUS
1234	1	2022-06-02 00:35:00	In Progress
1231	5	2022-06-02 00:30:00	Draft