How would I get the non-latest record for each name. I want to get results such that if a name has one record, it is excluded because it is the latest. If a name has two records, I'll include the older one. If a name has three records, It'll include the older two.
If I have a table like this
+-------+---------------------+---------+
| name | date | picture |
+-------+---------------------+---------+
| jose | 2020-12-11 09:27:24 | 1.jpg |
| ned | 2021-12-10 09:27:31 | 20.jpg |
| ned | 2018-12-25 09:27:34 | 55.jpg |
| sid | 2017-12-20 09:28:21 | 21.jpg |
| ned | 2021-12-19 09:27:34 | 22.jpg |
| sid | 2015-12-15 09:28:21 | 66.jpg |
| wade | 2014-12-17 09:28:21 | 88.jgg |
| wade | 2019-12-18 09:28:21 | 11.jpg |
| wade | 2021-12-19 09:28:21 | 10.jpg |
| wade | 2022-12-05 09:28:21 | 20.jpg |
+-------+---------------------+---------+
The results should be
+-------+---------------------+---------+
| name | date | picture |
+-------+---------------------+---------+
| ned | 2021-12-10 09:27:31 | 20.jpg |
| ned | 2018-12-25 09:27:34 | 55.jpg |
| sid | 2015-12-15 09:28:21 | 66.jpg |
| wade | 2014-12-17 09:28:21 | 88.jgg |
| wade | 2019-12-18 09:28:21 | 11.jpg |
| wade | 2021-12-19 09:28:21 | 10.jpg |
+-------+---------------------+---------+
This problem can be solved using window function row_number in next way:
with ordered as (
select
t.*, row_number() over (partition by name order by date desc) rn
from t
) select * from ordered where rn > 1;
The row_number returns numbers in order of date, sow the oldest record always will be numbered by 1, after that we simply filter out such records