|
altisdesign
Junior Member
United Kingdom
357 Posts |
 Posted - 26 August 2003 : 07:08:12
|
I've tried to get this select query to work, which for me is quite complicated, but whatever I try I cannot make it successfully select the records I want. I've searched google, but havnt found much on group by and sub queries.. or at least what I need  so i wandered if someone here might be able to help me.
I have a table called 'teamperformance' which logs the performance of teams over a number of weeks, with the following fields:
- teamperformanceid - primary key, autonumber field
- teamid - id of the team (which I will use to LEFT JOIN to another table called team to get the "team" information)
- schoolposition - integer field, position in the league
- yearposition - integer field, position in the league within the year
- pointsthisupdate - the weekly points
- totalpoints - total points added up
- date - the date the points were awarded
What I'm trying to do is display a league table of the performance of the team, based on the top record of the database (which has the totalpoints information which I need to display).
Because there will be more than 1 record in the database per team, I thought I could use "SELECT DISTINCT teamid" to select only the unique team ids, but that didnt seem to work, so instead I used "GROUP BY teamid". That only selected unique teamids, but I cant find any way to control what record is selected (it seems to select the first performance, not the latest one). I looked into HAVING to control this, eg. "HAVING teamperformance.date = max(teamperformance.date" but that gave SQL errors. I tried using a subquery in SQL to select the top record eg.
SELECT teamperformance.teamid, teamperformance.pointsthisupdate, teamperformance.totalpoints, teamperformance.yearposition, managername, teamname
FROM teamperformance
LEFT JOIN team ON team.teamid = teamperformance.teamid
WHERE team.year = 7 and date = '(SELECT max(date) FROM teamperformance AS teamp WHERE teamp.teamid=teamperformance.teamid)'
GROUP BY teamperformance.teamid
ORDER BY teamperformance.yearposition ASC
but that didnt seem to return anything just an empty recordset.
Can anyone help me out with this?, many thanks in advance
-Altis Design
|
Altis Design offers all manner of web design services to a variety of commercial and personal clients |
|
Topic Locked
