most recent 5 entries for each team

[NigeB]

I am trying to create a query which will return the most recent 5 entries for each Team

The table is team, reg_no, amount, date

The data is entered in all sorts of dates, so one team may have their most recent 5 entries spanning 2 years, whilst another may have the most recent 5 entries spanning 2 weeks.

I was thinking along the lines of Distinct, but how would I limit the return to 5 entries for each team?

The table Teams holds the team name and data pertaining to each team, I am only interested in the Team Name from that table.

The data1 table holds the reg_no, amount and date

Here's what I tried, but I can't seem to get the DISTINCT statement intergrated.

Any help is appreciated.

Nige.

$query2 = "SELECT Distinct Team FROM Teams";
$row = mysql_fetch_assoc($query2,$db);

$query3 = " CREATE TEMPORARY TABLE tmp1 SELECT * from data1
LEFT JOIN data1 ON Teams.reg_no = data1.reg_no
WHERE Teams.Team='$row["Team"]'
ORDER BY data1.Date DESC
LIMIT 5";
$mysql_result2 = mysql_query ($query3, $db);

 

[sleipnir214]

Although it may be possible to get the information you want in a single query, why make yourself crazy?

Just perform the "SELECT DISTINT Team FROM Teams" and loop through the return.

On each iteration of the loop, perform something like:

SELECT
*
FROM
Teams
WHERE
Team = '<team identifier>'
ORDER BY
date desc
LIMIT 5


BTW using &quot;date&quot; as the name of a column is a very bad idea. It is a MySQL reserved word.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[NigeB]

It's not the Team selection thats the problem, it's using the team name to select 5 of _each_ teams records. whilst I have tried looping, I just get 5 records, not 5 from each team.

How do I get the second part of the query to run through each iteration through the loop?

TIA

Nige.

p.s. I realised all too late about the &quot;date&quot; word, unfortunatly the database is in excess of 30,000 records with numerous links and queries using the date value.

 

[sleipnir214]

Are you using the &quot;WHERE Team = '<team identifier>'&quot; part of my suggested query?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!