I have a table of 'matches' :
id match_date home_team away_team
---------------------------------------
1 2012-10-01 Milan Real
2 2013-01-05 Real PSG
3 2013-04-05 Inter Lacio
4 2013-07-10 Ajax Milan
---------------------------------------
Getting the previous matches is challenging, because of the home team/away team split. Instead, let's just focus on one team at a time, by having a separate record for each team.
The following gets the previous matchid for each team in the above table:
select id, match_date, team,
lag(id) over (partition by team order by match_date) as prev_matchid
from ((select id, match_date, home_team as team, 'home' as which
from matches
) union all
(select id, match_date, away_team as team, 'away' as which
from matches
)
) m;
You can join in the information about the match, if you like.
select id, match_date, team, lag(id) over (partition by team order by match_date) as prev_matchid ?
from clause.FROM matches, without sub-selectsteam in matches.SELECT id, match_date, home_team, lag(id) over (partition by home_team order by match_date) as prev_matchid FROM matches - is there any difference?You should be able to use the Max function to get the previous match. By selecting the max date that is less than today, you should be able to get the previous match.
Select Max(match_date), home_team, away_team
from matches
where match_date < current_date
To get the previous matches, you can order by match date
Select Match_date, home_team, away_team
from matches
order by match_date desc
where match_date < current_date
