1

Fixture Table

uid home_uid away_uid winner date       season_division_uid
1   26       6        6      2013-07-30 18
2   8        21       8      2013-06-30 18
3   6        8        8      2013-06-29 18
4   21       26       21     2013-05-20 18
5   6        26       6      2013-04-19 18

This table contains hundreds of rows.

Currently I have a query to select all the teams in a division, i.e. 

SELECT team_uid 
FROM Season_Division_Team 
WHERE season_division_uid='18'
  • which lists the rows of team uid's i.e. [6,26,8,21,26].

Now for each of the unique team ids, I would like to return the last 3 winner values, ordered by the date column, that they were involved in (they could be an away_uid or home_uid).

So the returned value example would be:

team_id winner date
6       6      2013-07-30
6       8      2013-06-29
6       26     2013-04-19
26      6      2013-07-30
26      21     2013-05-20
26      6      2013-04-19

Any ideas? Thank you

Justin
  • 9,634
  • 6
  • 35
  • 47
badgers
  • 23
  • 3
  • What did YOU try so far? – Germann Arlington Jul 30 '13 at 13:33
  • I'm looking for the best approach to try and achieve this as a starting point. This isnt a situation that has occured for me before – badgers Jul 30 '13 at 13:36
  • The query will be fairly complex (with sub-selects) because you are looking to return subsets (by number of records) from each set (by team ID). Write your query to return ALL records for each team in required order and then introduce sub-selects to limit the number of results returned per team. – Germann Arlington Jul 30 '13 at 13:45

1 Answers1

0

Im not sure how to get it direct, a query like

select * from Season_division_Team  where 
`date >= (select min(`date`) from 
(select `date` from season_division_team order by date desc limit 3)) 
 and (home_uid = 6 or away_uid = 6)

Thats not going to be a good query. But only way i can think of currently

Its hard to get the 3rd largest value from SQL Example

the sub query is trying to get the date where the last win occured, and then getting all dates after that where the team played.

EDIT:

SELECT * FROM Season_Division_Team WHERE winner = 6 ORDER BY `date` DESC LIMIT 3

that sounds more like your latter comment

Community
  • 1
  • 1
exussum
  • 18,275
  • 8
  • 32
  • 65
  • I agree - It IS not a good query. Not to mention that it does not help the OP to think either – Germann Arlington Jul 30 '13 at 13:41
  • I'm not sure if I have explained myself correctly in my original post. I am looking to extract the 3 rows from the Fixture table, for each of the team uids I have, which could either be in the home_uid or away_uid column - ordered by the date column. – badgers Jul 30 '13 at 13:46
  • so you only want 3 rows and not the 6 you have in your example ? – exussum Jul 30 '13 at 13:51
  • 3 rows per team. I have only added a subset of the data to not make the post too long. So for example, if there are 8 teams in a division, it will return 24 rows - 3 rows for each team. – badgers Jul 30 '13 at 13:55
  • You could just use a loop on your [6,26,8,21,26] array with the query user1281385 posted. There really is no need to overcomplicate this with strange sql querries. – cptnk Jul 30 '13 at 14:00
  • @badgers so last 3 games per team ? regardless of resulty ? – exussum Jul 30 '13 at 14:05
  • @user1281385 - Yes thats correct. To be honest I think cptnk is right. Im just going to iterate through the list and use the following query: SELECT winner, date FROM Fixture WHERE home_uid = '9' OR away_uid='9' ORDER BY date DESC LIMIT 5 – badgers Jul 30 '13 at 14:08