I have a table like this

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"

CodePudding user response：

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