select subset of rows-CodePudding

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....

CodePudding user response：

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

CodePudding user response：

We could do:

WITH max_date_table AS
(
SELECT MAX(DATE) AS max_date_value, NAME
FROM mytable
GROUP BY NAME
)
SELECT mytable.ID,  mytable.NAME, mytable.DATE, mytable.VALUE
FROM mytable 
INNER JOIN max_date_table
ON mytable.NAME=max_date_table.NAME
WHERE mytable.DATE != max_date_table.max_date_value