CODEWITHSUNDEEP
mysqlsql
Justin Pfenning
Justin Pfenning

Reputation: 543

Getting last records by date, but for 2 different ID's

I am trying to select the rows that have the last gameDate, todays gameDate (if exists), and the next gameDate from a table called games for any given team(s). It's structure is as follows:

awayTeam  homeTeam  gameDate  
    1         2     5/12/16
    2         3     5/13/16
    3         5     5/14/16
    2         4     5/14/16

The problem I am facing is that the teamID can appear in two columns - awayTeam or homeTeam. I saw a lot of solutions that have the group by clause but that only appears to work for just one column. I would like to get the row which corresponds to the last gameDate for each team. For instance, (1,5) would return:

awayTeam  homeTeam  gameDate
   1          2     5/12/15
   3          5     5/14/16

Since I want the most recent games, any games from today, and the next game, I was thinking the best way to solve this would be to first get the past rows, then UNION ALL with the rows from todays date, then UNION ALL with the next game. This table is small with only about 3,000 rows. I have the below query, but it only works for homeTeam and not if it appears in awayTeam. Also, the below query takes 2.2 seconds, which seems rediculously high for a table with such a small number of rows.

SELECT g . *
FROM games g
WHERE g.gameDate = (
SELECT MAX( g2.gameDate )
FROM games g2
WHERE g2.homeTeam = g.homeTeam
AND gameDate < NOW( )
AND g.homeTeam
IN (1, 5) )
ORDER BY g.gameDate DESC

I thought about perhaps splitting this into a view so I could easily get the last time a team has played, regardless of whether they appear in the homeTeam or awayTeam column, but that seems like overkill. This is for MySQL. Thanks for your time.

Upvotes: 1

Views: 60

Answers (1)

SQLChao
SQLChao

Reputation: 7847

This isn't pretty but it may help you. The inner most derived table gets all the teams in one column along side their dates. The next part gets each teams last game played date. Join that back to the original table and now you have the most recent last game for each team back in the original format. It's confusing to explain but really quite simple if you run each of the selects one by one working from inner to outer.

SQL Fiddle Demo

SELECT yt.*
FROM 
  (SELECT
    Team
  , MAX(GameDate) AS GameDate
  FROM
    (SELECT AwayTeam AS Team, GameDate
    FROM YourTable
    UNION ALL
    SELECT HomeTeam AS Team, GameDate
    FROM YourTable) a
  WHERE GameDate < NOW()
  GROUP BY
    Team) MaxDates
JOIN YourTable yt ON (MaxDates.Team = yt.AwayTeam OR MaxDates.Team = yt.HomeTeam) 
  AND yt.GameDate = MaxDates.GameDate 
WHERE MaxDates.team in (1,5)

Upvotes: 1

Related Questions