Question 90373 in PostgreSQL Question posted in
The official documentation can be found here.

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

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  |
+-------+---------------------+---------+
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search