Challenge while using row_number()

[Mikael Eriksson]

Hi!

Consider the following query. There might be some typos in it since I renamed the fields and tables, but I think that the problem will be clear anyway.

SELECT row_number() over
(ORDER BY (f7+ifnull(table1.points,0)) desc, f1 desc, f2 desc, f3 desc, f4 desc, f5 desc) as pos,
f6, id,(f7+ifnull(table1.p,0)) as cp, f1, f2, f3, f4, f5 FROM table2
LEFT JOIN table3 ON (table2.id = table3.id)
LEFT JOIN table1 ON (table2.id = table1.id)
WHERE gid = (SELECT max(gid) FROM table4 WHERE not(isnull(r1)))

My main concern is the order by-part.

I've renamed a column _ (f7+ifnull(table1.p,0)) as cp _

Alas, I cannot write _ ORDER BY cp desc _ since this results in the error message

ERROR 1054 (42S22): Unknown column 'cp' in 'window order by'

The column names are very nicely

pos | f6 | id | cp | f1 | f2 | f3 | f4 | f5

but the header cp is not recognized. Therefore the rather clumsy

ORDER BY (f7+ifnull(table1.points,0))

Anything to do about this?

Grateful for any reply.

--mike