Select teams' last N games

[SilkBC]

I have a problem similar to http://www.phpfreaks.com/forums/index.php?topic=325737.0;all, however the solution does not apply to my data because all my data is in one table, and the teams are not identified by an ID number (I import my data via CSV files that I download, and it would be cumbersome to replace the team names that are already in the CSV with the appropriate team ID numbers)

 

Is there a way that I would be able to run a query (plus I assume a sub query or two) to return the results of a team's last N games?  I already have a query that returns a table that shows each teams total record overall as well as home and away records; I want to add another section to this table showing their results for their last N games as well.

 

I was thinking I could create a temporary table, as I do to get the data for the seperate home and away tables, but I cannot get a query that returns each teams' last N games; only last N total.

 

Thanks for any help.  I can provide a dump of my current database and data if needed.

 

[kickstart]

Hi

 

I can think of a way to do it (although not sure how efficient).

 

Doing something similar on a table of mine the following works:-

 

SELECT *
FROM
(SELECT @rownum:=@rownum+1 rownum, testresults.id, testresults.Client, testresults.MonthYear
FROM (SELECT @rownum:=0) r, testresults
ORDER BY Client, MonthYear DESC) x
INNER JOIN (
SELECT Client, MIN(rownum) AS rownum
FROM
(SELECT @rownum:=@rownum+1 rownum, testresults.id, testresults.Client, testresults.MonthYear
FROM (SELECT @rownum:=0) r, testresults
ORDER BY Client, MonthYear DESC) x
GROUP BY Client) z
ON x.Client = z.Client
WHERE x.rownum < z.rownum + 10

 

This is using a subselect to get all the records with a row number on them in descending date order. The subselect is also used to get the the smallest row number for each client. The 2 are joined together and only records where the row number is less that the min row number plus 10 (ie, the latest 10) is retrieved.

 

There might well be a more efficient way to do this.

 

If you can supply a layout of your table and some example data I will try and modify it to work for what you want.

 

All the best

 

Keith

[SilkBC]

Thanks for your reply, Keith.

 

I have been playing around with some MySQL queries and PHP code, and I am able to perform a query that gets a list of the teams for the current season, and I am thinking that if those teams could be put into an array, I could then perform a query that inserts each team's last 8 results (home or away) into a temporary table, then do my calcs from there.

 

I tried attaching a dump of my database with all the data in it, but was told the file folder was full (it is only 276K zipped up); is there another way I can send you the data?

 

-SilkBC

[kickstart]

Hi

 

Think doing it in 2 stages with a temp table would be way less efficient.

 

All the best

 

Keith

[fenway]

Have you considered this?

[SilkBC]

Have you considered this?

 

I have seen that article, but it uses an "ID" key which my table does not have.  My data is from downloaded CSV files that I then import into my database.  All the data is re-imported each time (the existing table is dropped, re-created, and the CSV data is then imported).  Because game information is added to the CSV files all the time, many with the same date, keeping the IDs the same from import to import would not be guaranteed.

 

I am looking for a solution that does not require the use of a key (or "ID") column.  It should be possible to do it by finding each team's last N games based on date, since a team does not play more than one game on any particular date.  Everything I have tried thus far, though, results on only the last N games total played, regardless of team.

[SilkBC]

Think doing it in 2 stages with a temp table would be way less efficient.

 

Since I cannot seem to upload a dump of my database, is there another way I can get you the data?  I could put a zip file on a server somewhere and either post the link here or PM you?

 

-SilkBC

[fenway]

Have you considered this?

 

I have seen that article, but it uses an "ID" key which my table does not have.  My data is from downloaded CSV files that I then import into my database.  All the data is re-imported each time (the existing table is dropped, re-created, and the CSV data is then imported).  Because game information is added to the CSV files all the time, many with the same date, keeping the IDs the same from import to import would not be guaranteed.

 

I am looking for a solution that does not require the use of a key (or "ID") column.  It should be possible to do it by finding each team's last N games based on date, since a team does not play more than one game on any particular date.  Everything I have tried thus far, though, results on only the last N games total played, regardless of team.

The ID is just a unique identifier -- any one you can come up with, including examining multiple fields, is equivalent.