MySQL Forums
Forum List  »  Newbie

Challenge while using row_number()
Posted by: Mikael Eriksson
Date: January 08, 2022 07:08AM

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

Options: ReplyQuote


Subject
Written By
Posted
Challenge while using row_number()
January 08, 2022 07:08AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.