How can I retrieve the rows of all non-latest records whereby the latest is always excluded?-CodePudding

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

CodePudding user response：

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;

https://sqlize.online/s/TW

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

CodePudding user response：

a variant of @slava solution is using array_agg instead of row_number :

SELECT name
     , unnest((array_agg(date ORDER BY date DESC))[2:]) AS date
     , unnest((array_agg(picture ORDER BY date DESC))[2:]) AS picture
  FROM my_table
 GROUP BY name

[2:] excludes the first value in the array which is associated to the highest date because the array values are ORDERed BY date DESC

see dbfiddle