MySQL CASE WHEN where the statement fails

Question

I have a complex query that performs multiple matches across multiple columns, and then sorts by relevance.

Everything works fine until I add WHERE 'rank' > 0

Then an empty result set is returned.

If I delete the WHERE statement, I can see all the results with the highest matches at the top.

Can someone help me figure out “WHERE”: -D I'm wrong!

SELECT *, CASE WHEN companyName = 'gfdgfs' THEN 2 ELSE 0 END 
+ CASE WHEN companyName LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
+ CASE WHEN companyName = 'potato' THEN 2 ELSE 0 END 
+ CASE WHEN companyName LIKE '%potato%' THEN 1 ELSE 0 END 
+ CASE WHEN address1 = 'gfdgfs' THEN 2 ELSE 0 END 
+ CASE WHEN address1 LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
+ CASE WHEN address1 = 'potato' THEN 2 ELSE 0 END 
+ CASE WHEN address1 LIKE '%potato%' THEN 1 ELSE 0 END 
AS rank 
FROM clients 
WHERE rank > 0
ORDER BY rank

EDIT

I removed the single quotes around the word rankand now got an "unknown column rank in where where"

Answer 1

rank . , , MySQL WHERE - this.

HAVING, WHERE:

SELECT *, CASE WHEN companyName = 'gfdgfs' THEN 2 ELSE 0 END + CASE WHEN companyName LIKE '%gfdgfs%' THEN 1 ELSE 0 END + CASE WHEN companyName = 'potato' THEN 2 ELSE 0 END + CASE WHEN companyName LIKE '%potato%' THEN 1 ELSE 0 END + CASE WHEN address1 = 'gfdgfs' THEN 2 ELSE 0 END + CASE WHEN address1 LIKE '%gfdgfs%' THEN 1 ELSE 0 END + CASE WHEN address1 = 'potato' THEN 2 ELSE 0 END + CASE WHEN address1 LIKE '%potato%' THEN 1 ELSE 0 END AS rank FROM clients HAVING rank > 0 ORDER BY rank

Answer 2

:

SELECT * 
FROM (SELECT *, CASE WHEN companyName = 'gfdgfs' THEN 2 ELSE 0 END 
              + CASE WHEN companyName LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
              + CASE WHEN companyName = 'potato' THEN 2 ELSE 0 END 
              + CASE WHEN companyName LIKE '%potato%' THEN 1 ELSE 0 END 
              + CASE WHEN address1 = 'gfdgfs' THEN 2 ELSE 0 END 
              + CASE WHEN address1 LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
              + CASE WHEN address1 = 'potato' THEN 2 ELSE 0 END 
              + CASE WHEN address1 LIKE '%potato%' THEN 1 ELSE 0 END 
              AS rank 
        FROM clients 
      ) AS A 
WHERE rank > 0
ORDER BY rank;