How to fetch status column based on max/min of a timestamp column

user_19619813 0
Viewed 35

I have a table like this

Emplid REQUEST_ID Status Status_Dttm
1234 1 Open 02-Jun-2022 12.35.00.AM
1231 5 Draft 02-Jun-2022 12.30.00.AM
1234 1 In Progress 02-Jun-2022 12.47.00.AM

How can I get request date as Min(Status Dttm) and current status as Max(Status Dttm) of every request

I tried using this SELECT EMPLID,REQUEST_ID,MIN(Status_Dttm) AS "REQUEST DATE", (SELECT STATUS FROM STS_TBL WHERE MAX(Status_Dttm)) AS "CURRENT_STATUS" FROM STS_TBL;

getting error as "Group function is not allowed here"

oracle
1 Answers

You want to add GROUP BY request_id if you want to group by each request:

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

Which outputs:

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

If the request_id is the same for each emplid (which appears to be the case for your sample data) then you could use:

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

Which outputs:

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

db<>fiddle here

user_15092640
Related