Last N records by each grouped item

[MargateSteve]

I have a query which generates a soccer standings table and am trying to create a different version which will only include the 6 most recent games for each team.

An example of the output of my current query can be seen HERE. The full query is below and I have also attached a dump of the relevant tables in a text file.

I have asked the question many times on other forums and have googled 'Show Last N records per group' for months, including another 2 hour spell just now but everything I have tried just causes empty results.

To be honest, all of the suggestions I have looked at are for just one table so I think that with 3 tables being used in my query, the whole thing becomes more complex. It is, however, possible as most UK soccer sites have this (known as a 'Current Form' Table) and an example can be found at http://www.andysstats.co.uk/form.asp?division=P&type=LT&season=200910. Out of interest, the right-most column on that table shows the actual outcome of that team's last 6 games (W for won, L for Lost etc) and that is something else I would be looking at doing eventually.

Any suggestions or pointers would be gratefully received as always.
Thanks in advance
Steve

THE MAIN QUERY

$i = 1;

/*ADD th, st, rd TO THE POSITION NUMBERS*/
/*
 * @return number with ordinal suffix
 * @param int $number
 * @param int $ss Turn super script on/off
 * @return string
 */
function ordinalSuffix($i, $ss=0)
{
    /*** check for 11, 12, 13 ***/
    if ($i % 100 > 10 && $i %100 < 14)
    {
        $os = 'th';
    }
    /*** check if number is zero ***/
    elseif($i == 0)
    {
        $os = '';
    }
    else
    {
        /*** get the last digit ***/
        $last = substr($i, -1, 1);

        switch($last)
        {
            case "1": 
            $os = 'st';
            break;

            case "2":
            $os = 'nd';
            break;

            case "3":
            $os = 'rd';
            break;

            default:
            $os = 'th';
        }
    }

    /*** add super script ***/
    $os = $ss==0 ? $os : '<sup>'.$os.'</sup>';

    /*** return ***/
    return $i.$os;
};

/*GET THE CORRECT SEASON*/
if (isset($_GET['season_id']) ? $_GET['season_id'] : 1);
$season_id = $_GET['season_id'];

/*COMMON STANDINGS QUERY STRINGS*/
$ht = "g.home_team = t.team_id";
$at = "g.away_team = t.team_id";
$hw = "g.home_goals > g.away_goals";
$aw = "g.home_goals < g.away_goals";
$d = "g.home_goals = g.away_goals";
$hg ="g.home_goals";
$ag ="g.away_goals";

/*THE STANDINGS QUERY*/ 
$table = mysql_query("SELECT *,
t.team_name as Tm
, @rownum := @rownum+1 AS rank
, ((sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END)
+ sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)))  AS P
, count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L
, SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF
, SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA
, (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END)
- SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD
, d.change as DIFF
from teams t
left join all_games g 
on t.team_id in (g.home_team,g.away_team)
LEFT JOIN seasons as S ON g.date BETWEEN S.season_start AND S.season_end
LEFT JOIN deductions d ON ( d.team = t.team_id) AND (d.season = S.season_id)
WHERE comp = '1' AND home_goals IS NOT NULL 
AND S.season_id = $season_id 
GROUP BY t.team_id
ORDER BY P desc, GD desc, GF desc 
");

[smantscheff]

Which parameter value have you set for season_id ? How and where do you set an initial value for rank (@rownum)?

This query code works for me (i.e. it delivers results which seem to be structurally o.k.):

set @rownum = 0;
SELECT *,
t.team_name as Tm
, @rownum := @rownum+1 AS rank
, ((sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals > g.away_goals)OR(g.away_team = t.team_id AND g.home_goals < g.away_goals) THEN 3 ELSE 0 END)
+ sum(CASE WHEN (g.home_team = t.team_id OR g.away_team = t.team_id) AND g.home_goals = g.away_goals THEN 1 ELSE 0 END)))  AS P
, count(CASE WHEN (g.home_team = t.team_id OR g.away_team = t.team_id) THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals > g.away_goals) OR (g.away_team = t.team_id AND g.home_goals < g.away_goals) THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals = g.away_goals) OR (g.away_team = t.team_id AND g.home_goals = g.away_goals) THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals < g.away_goals) OR (g.away_team = t.team_id AND g.home_goals > g.away_goals) THEN 1 ELSE 0 END) AS L
, SUM(CASE WHEN (g.home_team = t.team_id) THEN g.home_goals WHEN (g.away_team = t.team_id) THEN g.away_goals END) as GF
, SUM(CASE WHEN (g.home_team = t.team_id) THEN g.away_goals WHEN (g.away_team = t.team_id) THEN g.home_goals END) as GA
, (SUM(CASE WHEN (g.home_team = t.team_id) THEN g.home_goals WHEN (g.away_team = t.team_id) THEN g.away_goals END)
- SUM(CASE WHEN (g.home_team = t.team_id) THEN g.away_goals WHEN (g.away_team = t.team_id) THEN g.home_goals END)) as GD
, d.change as DIFF
from teams t
left join all_games g 
on t.team_id in (g.home_team,g.away_team)
LEFT JOIN seasons as S ON g.date BETWEEN S.season_start AND S.season_end
LEFT JOIN deductions d ON ( d.team = t.team_id) AND (d.season = S.season_id)

WHERE comp = '1' 
AND home_goals IS NOT NULL  
AND S.season_id =  103
GROUP BY t.team_id
ORDER BY P desc, GD desc, GF desc

[MargateSteve]

Sorry for not getting back sooner but I have had a busy weekend!

Which parameter value have you set for season_id ?

In the actual working file the season_id is posted but for this example I have I am just using a static one which relates to the 2010 season.

How and where do you set an initial value for rank (@rownum)?

Ignore that as I have mistakenly left it in from an initial attempt to get a ranking. The rank is actually done by the 'ordinalSuffix' function and

while ($row_table = mysql_fetch_assoc($table)){
$rankings_arr[$i] = $row_table;
echo ordinalSuffix($i);

This query code works for me (i.e. it delivers results which seem to be structurally o.k.):

The query does actually work showing all the games for every team in the relevent season but I just want to show each teams last 6 games.

I was pointed to this tutorial http://explainextended.com/2009/03/05/row-sampling/ but as that is just relating to one table, I am completely confused how to get it to work with three joined tables.

In essence, what I need to do is take each team separately, get only the last 6 games for each team, and then recreate the table from those results. It seems as simple as there should be a subquery of SELECT each teams games ORDER BY date desc LIMIT 6 but all I ever can get back is the last 6 games in total, not the last 6 for each team.

Any suggestions would be gratefully received!
Thanks for taking the time to look at this.
Steve

[almostbob]

may take a two part query
not formalised as code, just a thought exercise

firstarray=select from team teamid
secondarray= for each in firstarray select from games where teamid=firstarray[team] order by date desc limit 6

properly constructed would create a 2d array where rows are by teamid, and columns are team id and last 6 games

[MargateSteve]

may take a two part query
not formalised as code, just a thought exercise

firstarray=select from team teamid
secondarray= for each in firstarray select from games where teamid=firstarray[team] order by date desc limit 6

properly constructed would create a 2d array where rows are by teamid, and columns are team id and last 6 games

That looks like a good potential solution but would not know where to start!
When you say 'for each' I presume you don't mean a php loop statement? Would a two part query take the form of a sub query of the secondarray being the sub of the firstarray.

Excuse my naivety with this but I am starting to get way out of my league with this, but am hoping to understand!

Thanks
Steve

[urtrivedi]

I have tried doing in one query, but Not able to do it in your case, I was able to succced in other cases long back ago. So as almostbob suggested I am posting part query code which is mysql php combination (uncompiled). You may try this

<?PHP
$query="select * from team order by team_id";

$web=mysql_query($query);
echo "\n<table>";

   echo "\n<tr><td><b>team name</b></td>";

   echo "\n";
   echo "<td>col1</td>";
   echo "<td>col2</td>";
   echo "<td>col3</td>";
   echo "</tr>";

while($rowweb=mysql_fetch_assoc($web))
{

   $query="select ....... where .... and team_id='{$rowweb[team_id]}' order by date desc limit 6"; //...means your original query that you have posted with additional teamid filter and limit 6 phrase

   $rec=mysql_query($query);


   while($rowrec=mysql_fetch_assoc($rec))
   {
      echo "\n<tr>";
      echo "<td>{$rowrec['col1']}</td>";
      echo "<td>{$rowrec['col2']}</td>";
      echo "<td>{$rowrec['col3']}</td>";
      echo "</tr>";

   }

   echo "\n<tr><td><b>&nbsp</td></tr>";
}


?>

This may not give you result in order of points.
for that:
1) You may have to store all result in array,
2) perform array sort and
3) display array