MySQL - rank function inside where clause

Question

I want to use rank function in my MySQL query. I used advice from Rank function in MySQL

I want to select only one row with highest rank. My query looks like:

SELECT c1, c2, c3, @curRank := @curRank + 1 AS rank 
FROM tab1 t, (SELECT @curRank := 0) r 
WHERE t.c5 = 'asd' AND t.c6 = 'qwe' AND rank = 1 
ORDER BY t.c8 DESC

And I receive following error:

ERROR 1054 (42S22): Unknown column 'rank' in 'where clause'

What am I doing wrong? How I can fix my query?

Answer 1

You cannot use the ALIAS that was generate on the same level as the WHERE clause, you can wrap it with subquery. Eg,

SELECT *
FROM
(
   SELECT c1, c2, c3, @curRank := @curRank + 1 AS rank
   FROM tab1 t, (SELECT @curRank := 0) r 
   WHERE t.c5 = 'asd' AND t.c6 = 'qwe' 
   ORDER BY t.c8 DESC
) a
WHERE rank = 1

The SQL Order of Operation is as follows:

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

The ALIAS is created on the SELECT clause so it is not yet accessible on the WHERE clause.