0

I have 2 tables. I want to join in a way that the latest date is joined for that player. From the below subset, I want the join for Adams to only join to the row with the draw because it was the latest date-wise. How do I go about doing this when you have multiple rows?

ID    PLAYER
-------------
2     Adams
3     Rogers
4     David


PLAYER_ID    DATE          RESULT
------------------------------------
2            01/01/2014    Win
2            01/02/2014    Win
2            01/03/2014    Loss
2            01/04/2014    Draw
KingKongFrog
  • 13,946
  • 21
  • 75
  • 124
  • 1
    is the combination of player_ID and Date unique? – Twelfth Sep 24 '14 at 22:21
  • What RDMS are you using? – Arun Sep 24 '14 at 22:21
  • I want to be able to basically join on the MAX of the array by date. I'm using postgres. – KingKongFrog Sep 24 '14 at 22:24
  • I don't know much about PostgreSQL, but it seems it has a convenient syntax for this issue: [PostgreSQL GROUP BY: SELECT column on MAX of another WHERE a third column = x](http://stackoverflow.com/questions/14803656/postgresql-group-by-select-column-on-max-of-another-where-a-third-column-x) – GolezTrol Sep 24 '14 at 22:40

1 Answers1

1

I'm assuming player_id and date is a unique combination. Date isn't a good column name. I've assumed your tables names are player and result...in the future, give us table names so answers can include them instead of my guesses.

Building the query:

(select player_ID, max(date) from table group by player_ID)

That will select the maximum date for each player. We are going to use it as a subquery and inner join to it so it acts as a filter. Note that if the player_ID, date combination isn't unique, this will bring back multiple rows per player.

Select player, maxdate
from player p
inner join (select player_ID, max(date) as maxdate from result group by player_ID) a
on a.player_ID = p.player_ID

This will return to you a player and the most recent date. Join that back to the result table to get the result

Select player, result
from player p
inner join (select player_ID, max(date) as maxdate from result group by player_ID) a
on a.player_ID = p.player_ID
inner join result r on r.player_ID = a.player_ID and r.date = a.maxdate

Hopefully that makes sense as to the logic in creating the statement for you.

Twelfth
  • 7,070
  • 3
  • 26
  • 34