Re: Selecting records with highest timestamp - for a join

​And if the second most recent has a picture but the most recent one does not?  Do you want to accept the missing value because its on a more recent record or do you want to take the most recent non-missing value?

​Assuming "most recent not missing" and given:

​You basically want:

SELECT s_id, first_nonnull(photo ORDER BY stamp DESC, social)

FROM ...

GROUP BY s_id

You need to write a custom first_nonnull function that ignores NULL and a custom aggregate to go along with it.  Examples abound on the Internet.

Note that the Window function first_value doesn't quite do this...you want to constrain the result to be non-null unless all candidate values are null (or there are none).

If you have a unique index on (sid, stamp) you could solve the alternative problem with a simple (sid, max(timestamptz) join back against the social table.

David J.

​