2

Having the following two tables:

Players

id | player
-----------
 1 | ABC
 2 | CDE
 3 | FGH

Games

id | player_id | created_at
-------------------------------------
 1 | 1         | 2019-09-01 15:00:00
 2 | 1         | 2019-09-15 17:00:00
 3 | 2         | 2019-10-01 15:00:00
 4 | 2         | 2019-10-05 18:00:00
 5 | 2         | 2019-10-12 15:00:00

How can I select all players and show their latest if they have any, or NULL if they never played a game? Something like this with this example:

player_id | created_at
--------------------------------
        1 | 2019-09-15 17:00:00
        2 | 2019-10-12 15:00:00
        3 | NULL
iminiki
  • 2,549
  • 12
  • 35
  • 45
rlcabral
  • 1,496
  • 15
  • 39
  • Take a look at [this answer](https://stackoverflow.com/a/27802817/4265352), [this answer](https://stackoverflow.com/a/36956474/4265352), [this answer](https://stackoverflow.com/a/29302379/4265352) and [this answer](https://stackoverflow.com/a/28090544/4265352) to similar questions. – axiac Oct 15 '19 at 13:48
  • Possible duplicate of [MySQL order by before group by](https://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by) – Rogue Oct 15 '19 at 13:48

1 Answers1

3

You can try below - use left join

select p.id, max(created_at)
from Players p left join Games g on p.id=g.player_id
group by p.id
Fahmi
  • 37,315
  • 5
  • 22
  • 31