Retrieving entire rows while filtering on an aggregate expression

Question

Given this data in table test,

ID	val	date
1	111	2022-01-01
1	99	2020-01-01
1	95	2021-01-01
2	32	1990-07-25
2	8	1991-12-25
3	100	2022-05-28
4	0	2022-05-28

I would like to retrieve the record with the most recent date for each ID:

ID	val	date
1	111	2022-01-01
2	8	1991-12-25
3	100	2022-05-28
4	0	2022-05-28

This query provides the desired result, but is it possible to do this without using a derived table?
In our actual schema, the inner query may be expensive, as date is not indexed.

select * 
from test, (select id, max(date) as maxdate from test group by id) test2
where test.id = test2.id and test.date = test2.maxdate

Answer 1

You can use analytic function

WITH
    test AS
        (
            SELECT 1 "ID", 111 "VAL", To_Date('2022-01-01', 'yyyy-mm-dd') "DT" FROM DUAL UNION ALL
            SELECT 1 "ID", 99 "VAL", To_Date('2020-01-01', 'yyyy-mm-dd') "DT" FROM DUAL UNION ALL
            SELECT 1"ID", 95 "VAL", To_Date('2021-01-01', 'yyyy-mm-dd') "DT" FROM DUAL UNION ALL
            SELECT 2 "ID", 32 "VAL", To_Date('1990-07-25', 'yyyy-mm-dd') "DT" FROM DUAL UNION ALL
            SELECT 2 "ID", 8 "VAL", To_Date('1991-12-25', 'yyyy-mm-dd') "DT" FROM DUAL UNION ALL
            SELECT 3 "ID", 100 "VAL", To_Date('2022-05-28', 'yyyy-mm-dd') "DT" FROM DUAL UNION ALL
            SELECT 4 "ID", 0 "VAL", To_Date('2022-05-28', 'yyyy-mm-dd') "DT" FROM DUAL 
        )
SELECT
    *
FROM
    (
        Select
            t.ID "ID",
            CASE WHEN t.DT = Max(t.DT) OVER(PARTITION BY t.ID ORDER BY t.ID) THEN t.VAL END "VAL",
            Max(t.DT) OVER(PARTITION BY t.ID ORDER BY t.ID) "DATE"
        From
            test t
    )
WHERE
    VAL Is Not Null
ORDER BY
    ID

Where column VAL in Select clause will have value only where the date is Max(DT) for ID, otherwise is null. So you just exclude nulls...