select subset of rows

I have the following table

ID  NAME    DATE        VALUE
1   John    2022-02-01  27
2   John    2022-02-01  9
3   John    2022-01-31  -12
4   John    2021-12-15  32
5   Tom     2021-10-22  11
6   Tom     2021-05-05  201
7   Tom     2020-11-27  76
8   Bob     2022-01-07  43
9   Bob     2021-08-26  3
10  Bob     2021-05-13  12
11  Bob     2021-03-08  2

I want to write a query that ignores the rows with the latest DATE for each NAME

ID  NAME    DATE        VALUE
3   John    2022-01-31  -12
4   John    2021-12-15  32
6   Tom     2021-05-05  201
7   Tom     2020-11-27  76
9   Bob     2021-08-26  3
10  Bob     2021-05-13  12
11  Bob     2021-03-08  2

No idea how to write a query like that….

Answer

You can use rank() to rank the rows by descending DATE, then filter out rows with rank = 1 :

select ID, NAME, DATE, VALUE from
(select *,
rank() over(partition by NAME order by DATE desc) r
from table_name) t
where r > 1;

Fiddle