Re: Selecting records with highest timestamp - for a join

On 10/19/2016 12:44 PM, Alexander Farber wrote:
> Adrian, for both player1 and player2 (because I need to display player
> photos above the game board).
>
> SQL join with words_social - yes, but how to take the most recent record
> from that table?
>
> For example there are user infos from Google+, Facebook, Twitter - but
> the user has used Facebook to login lately and would expect her
> Facebook-photo to be seen (the record with the highest "stamp" value).

I have not dug into your function deep enough to understand all the relationships
involved so I cannot offer anything specific. A generic method:

test[5432]=# create table ts_stamp_test(id serial PRIMARY KEY, uid integer,  stamp integer NOT NULL);
                                     
CREATE TABLE
                                     
test[5432]=# insert into ts_stamp_test (uid, stamp) values (1, 5), (2, 10), (1, 12), (2, 15), (1, 18), (2, 30);
INSERT 0 6
                                     
test[5432]=# select * from ts_stamp_test;
                                     
 id | uid | stamp
                                     
----+-----+-------
                                     
  1 |   1 |     5
                                     
  2 |   2 |    10
                                     
  3 |   1 |    12
                                     
  4 |   2 |    15
                                     
  5 |   1 |    18
                                     
  6 |   2 |    30
                                     
(6 rows)
                                     

                                     

test[5432]=# select * from ts_stamp_test where uid = 1 order by stamp desc limit 1;
 id | uid | stamp
----+-----+-------
  5 |   1 |    18
(1 row)


>
> Regards
> Alex
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com