Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

last 5 records for each group

Status
Not open for further replies.

NigeB

Technical User
Nov 29, 2000
53
GB
I have a database consisting of teams who compete irregularly, what I wish to do is query a table to return the most recent 5 results for each team, I can not use a date value (to select the records) as in the last month some teams may have competed 12 times while others only 5 times or less.

I need to return the actual records rather than just count the number of records that exist in the criteria.

The field names are basically team_name , points, and date (the later being the match date).

Any help or pointing in the right direction would be appreciated.

Nigel
 
i would try the LIMIT clause

select * from table limit 5

that should return the topmost 5 results, just change the select clause to whatever you need

in your case: select team_name, points, date from table where team_name='teamname' order by date desc limit 5
 
Unfortunatly this only returns the top 5 what I need is the top 5 for each team, the query is thus

SELECT Ttimes.TRN, Teams.Team, Min(Ttimes.Fastest_Time)
AS MinOfFastest_Time
FROM Teams
LEFT JOIN Ttimes ON Teams.TRN = Ttimes.TRN
LEFT JOIN Venues ON Ttimes.Tournament_ID = Venues.Tournament_ID
WHERE Ttimes.Date>DATE_SUB(CURDATE(), INTERVAL 120 DAY)
GROUP BY Ttimes.TRN, Teams.Team
ORDER BY MinOfFastest_Time Limit 5

Basically I wish to have the top 5 times for each team in the last 4 months, I can carry out the 4 months calculation in a seperate query and generate a temp table but can't return the top 5 entries for each team

Any ideas?

Regards

Nigel
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top