SELECT rank

[lukep11a]

I am trying to get the rank of each team direct from a select statement.

 

My ultimate goal is to select the rank of a team depending on the number of total points, and also the number of total points minus the last 7 days in the same select statement as to get the change in rank.

 

I am currently doing this using a select query inside a while loop which is loading very slowly as there are currently about 100 rows and think if it were in one statement it would load much quicker.

 

But I can't even get the rank for the total points. I thought if I did this first then I could use a join to also select the rank of the total points minus the last 7 days.

 

I was thinking something like this would work:

 

SELECT t1.team_id, SUM(t1.points) AS total1, COUNT(t2.team_id) AS rank
FROM team_points t1
LEFT JOIN
   (SELECT team_id, SUM(points) AS total2 
   FROM team_points 
   GROUP BY team_id) AS t2
ON t1.total1 < t2.total2
GROUP BY team_id
ORDER BY total DESC

But that's not right. It gives the error Unknown column 't1.total1' in 'on clause'. I am struggling to get my head around this. I have tried many other ways without success. Does anybody have any ideas? Or am I better off sticking to my old way?

 

[fenway]

Well, you're trying to use the alias of a colum in the SELECT list in the ON clause -- but the aliasing happens on the return, long after the JOIN.