MySQL - rank function inside where clause

mysql, rank, sql

Solution

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.

Problem

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?

Original source

Related problems