MySQL Case Statement - Unknown column in where

Question

I have a problem with my query, what's wrong?

SELECT 
    CASE 
        WHEN cp_pessoa.score < 100 THEN 1 
        WHEN cp_pessoa.score < 300 THEN 2 
        WHEN cp_pessoa.score >= 300 THEN 3 
    END as id_ranking
FROM cp_pessoa 
    WHERE id_ranking IN (1,2);

I get this error: #1054 - Unknown column 'id_ranking' in 'where clause'

Thank you!

Answer 1

It's because SELECT prepare result (as id_ranking) AFTER than WHERE was called.

SELECT 
    CASE 
    WHEN cp_pessoa.score < 100 THEN 1 
    WHEN cp_pessoa.score < 300 THEN 2 
    WHEN cp_pessoa.score >= 300 THEN 3 
END as id_ranking
FROM cp_pessoa 
WHERE cp_pessoa.score < 300;

This is how that works (order):

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

Answer 2

Aliases are not allowed in where. Change query to either

SELECT * FROM 
(
SELECT 
    CASE 
        WHEN cp_pessoa.score < 100 THEN 1 
        WHEN cp_pessoa.score < 300 THEN 2 
        WHEN cp_pessoa.score >= 300 THEN 3 
    END as id_ranking
FROM cp_pessoa 
)a 
WHERE id_ranking IN (1,2)

or

SELECT 
    CASE 
        WHEN cp_pessoa.score < 100 THEN 1 
        WHEN cp_pessoa.score < 300 THEN 2 
        WHEN cp_pessoa.score >= 300 THEN 3 
    END as id_ranking
FROM cp_pessoa 
WHERE 
  CASE 
        WHEN cp_pessoa.score < 100 THEN 1 
        WHEN cp_pessoa.score < 300 THEN 2 
        WHEN cp_pessoa.score >= 300 THEN 3 
    END IN (1,2)