group by confusion 1

[altisdesign]

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

 

[sleipnir214]

First of all, group by will do nothing unless you are using aggregation functions in the field to be selected. I'd remove that entire clause from the query.

Second, you're performing a subselect, which MySQL does not support unless you're running MySQL 4.1. MySQL AB classifies MySQL 4.1 as alpha-level software -- I strongly recommend you do not use it yet.

For a discussion of workarounds, see

http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html

Want the best answers? Ask the best questions:

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

TANSTAAFL!!

 

[altisdesign]

Ahh I see... oops I didnt read that Thanks - my host uses version 3.3x not sure what sub version. Thats saved me quite a lot of time.. I will break the query up into two stages.