CODEWITHSUNDEEP
mysqlsql
kassarin
kassarin

Reputation: 27

Unknown Column in field list when already in columns

I've seen the solutions on stackoverflow so far for this problem and each seem to be different.. So i've decided to open a new question.

My objective is to create a rank column using ascending damage...

SET @rownum := 0;

SELECT rank, damage, playerID FROM (
                        SELECT @rownum := @rownum + 1 AS rank, damage, playerID
                        FROM PlayerStats WHERE playerID > 20130000000000 ORDER BY damage DESC, modified ASC
                    ) as result WHERE playerID='20130000000004'

However, it reports back with 'unknown column "damage" in field list'

"playerID","ancientBossID","damage","eventEnergy"

"20130000000004","2012000000000382","19033","38"
"20130000000078","2012000000000019","0","30"
"20130000000066","2012000000000242","0",30"
"20130000000080","2012000000000024","270","25"
"20130000000041","2012000000000133","5808","2"
"20130000000071","2012000000000030","694","30"
"20130000000055","2012000000000189","0","30"

Upvotes: 1

Views: 678

Answers (2)

El_L
El_L

Reputation: 355

SELECT rank, damage, playerID,modified FROM (
                        SELECT ROW_NUMBER() over(ORDER BY damage DESC, modified ASC)  AS rank, damage, playerID,modified
                        FROM PlayerStats WHERE playerID > 20130000000000 
                    ) as result WHERE playerID='20130000000004'

Upvotes: 1

peterm
peterm

Reputation: 92785

Here is your query with cosmetic changes, main of which is initialization of @rownum in the subquery

SELECT rank, damage, playerID 
FROM 
(
  SELECT @rownum := @rownum + 1 rank, damage, playerID
    FROM PlayerStats, (SELECT @rownum := 0) n
   WHERE playerID > 20130000000000 
   ORDER BY damage DESC, modified
) r 
-- WHERE playerID='20130000000004'
ORDER BY rank

And it works perfectly fine. Here is SQLFiddle demo.

Upvotes: 0

Related Questions