Re: ORDER BY alias

[Michael Stassen]

As far as I know, you can't use an alias in a calculation outside of a 
HAVING clause.  You could work around this by adding the calculation to your
SELECT clause:

  SELECT kills.PlayerID, player.DeathsPerMinute,
         SUM(kills.Kills) AS Total,
         SUM(kills.Kills) * (1-player.DeathsPerMinute) AS rank
  FROM playerweaponkills AS kills, ETPlayerSummary AS player
  WHERE kills.PlayerID=player.PlayerID AND kills.WeaponID=17
  GROUP BY kills.PlayerID
  ORDER BY rank DESC LIMIT 5
Michael

Danielb wrote:

I'm trying to order by an alias in a multi table SELECT statement(Note
I've cut the statement down a bit to make it more readable):
SELECT kills.PlayerID, player.DeathsPerMinute, SUM(kills.Kills) AS Total
FROM playerweaponkills AS kills, ETPlayerSummary AS player WHERE
kills.PlayerID=player.PlayerID AND kills.WeaponID=17 GROUP BY kills.PlayerID
ORDER BY (Total*(1-player.DeathsPerMinute)) DESC LIMIT 5
When I run this I get the error:
#1054 - Unknown column 'Total' in 'order clause'
I take it the problem is that MySQL is unable to resolve the alias Total
when its used in this way with player.DeathsPerMinute? Is there any way I
can prefix Total to help it be resolved? The statement works fine with
ordering by either Total or (1-player.DeathsPerMinute) its when you try and
combine them in the above statement it freaks out.
Any ideas? I ideal want to order by:
(Total*(1-player.DeathsPerMinute))
Cheers,

Daniel



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]