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.
