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

Postgres latest column value from each row – Postgresql

I have a sql table which is being updated hourly through API. Sample is:

id time firstname lastname salary location country
1 2023-03-08 07:47:58 John 10000
1 2023-03-08 07:50:58 Lenny Phoenix USA
1 2023-03-08 07:55:58 5000

What i am looking in target is only 1 row that should contain all latest updated value, so the sample will look somewhat like this:

id time firstname lastname salary location country
1 2023-03-08 07:55:58 John Lenny 5000 Phoenix USA

Is there any way this can be achieved through Postgres query?

Please suggest.

Thanks in advance

Tags:

2

Answers


  1. 0

    Using array_agg can do the trick :

    array_agg with order and filter to group data into an array (only not empty data).

    In where filter, if you have null values then use where column is not null instead.

    to_json to transform the array into json then using ->-1 we get the latest element

    select id, max(time),
  to_json(array_agg(firstname order by time) filter (where firstname <> '' ))->-1 AS firstname,
  to_json(array_agg(lastname order by time) filter (where lastname <> '' ))->-1 AS firstname,
  to_json(array_agg(salary order by time) filter (where salary <> '' ))->-1 AS salary,
  to_json(array_agg(location order by time) filter (where location <> '' ))->-1 AS location,
  to_json(array_agg(country order by time) filter (where country <> '' ))->-1 AS country
from mytable
group by id

    Demo here

    Login or Signup to reply.
  2. 0

    Small example using array_agg, an order by and a filter:

    SELECT  id
    ,   MAX(time) as time
    ,   (array_agg(firstname ORDER BY time DESC) FILTER(WHERE firstname IS NOT NULL))[1]    firstname
    ,   (array_agg(salary ORDER BY time DESC) FILTER(WHERE salary IS NOT NULL))[1] salary
FROM        t1
GROUP BY id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search