On Wed, Oct 19, 2016 at 11:35 AM, Alexander Farber < alexander.far...@gmail.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.
