CODEWITHSUNDEEP
postgresql
dojogeorge
dojogeorge

Reputation: 1704

Postgres GROUP BY id, then show first and last values based on timestamp

I have a table in Postgres like so:

ID Timestamp Followers
1 2021-01-01 100
2 2021-01-01 200
2 2021-01-02 202
2 2021-01-03 204

I want to return a result grouped by the ID, with earliest follower count, and the latest follower count.

So result would be:

ID Max Follower Min Follower
1 100 100
2 204 200

The aim is to return the corresponding followers for the earliest timestamp and latest timestamp - as followers can go up and down in the in-between periods.

In MySQL I used a slightly hacky solution:

SUBSTRING_INDEX(GROUP_CONCAT(b.followers ORDER BY timestamp ASC),',',1) as old

Is there a better solution in Postgres?

Upvotes: 0

Views: 83

Answers (1)

user330315
user330315

Reputation:

Maybe I am missing something, that looks like a simple aggregation:

select id,  
       max(followers) as max_follower, 
       min(followers) as min_follower
from the_table
group by id
order by id;

Edit:

if you don't want "min" and "max" but "earliest" and "latest" you can do the following:

select id,  
       (array_agg(followers order by "timestamp" desc))][1] as latest_follower, 
       (array_agg(followers order by "timestamp"))[1] as earliest_follower
from the_table
group by id
order by id;

Upvotes: 2

Related Questions