Re: Selecting records with highest timestamp - for a join

On Wed, Oct 19, 2016 at 11:35 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> wrote:

> I.e. a user can have several records in the above table, but I always use
> the most recent one (the one with the highest "stamp") to display that user
> in my game.
>

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

PRIMARY KEY(sid, social)

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