Rank() function with WHERE clause display nothing MYSQL

June 1, 2022

So I have to find student max scores from each of their organization, I comes up with the solution by applying RANK() function:

SET SQL_SAFE_UPDATES = 0;

INSERT INTO interviewqs.details(scores,name,organization)
    VALUES
    (30,"Daniel","OWARD UNI"),
    (40,"Kayla","OWARD UNI"),
    (12,"Hope","ZELENSKY UNI"),
    (50,"Osman","ZELENSKY UNI"),
    (4,"Daniel","REWARD UNI"),
    (77,"Joe","REWARD UNI");
DESCRIBE interviewqs.details;

# Find the student with highest scores from each organization
    
SELECT DISTINCT organization,name,scores,
    RANK() OVER (PARTITION BY organization ORDER BY scores DESC)
        AS "rank"
        FROM details
        WHERE "rank" = 1;

The problem is when I executed the code the output display empty table,

Without ‘WHERE’ function applied

organization name scores rank 
OWARD UNI   Kayla   40  1
OWARD UNI   Daniel  30  3
REWARD UNI  Daniel  77  1
REWARD UNI  Daniel  30  2
REWARD UNI  Daniel  4   4
ZELENSKY UNI Osman  50  1
ZELENSKY UNI Hope   12  3

With ‘WHERE’ function applied

organization name scores rank

What mistake did I do here?

>Solution :

You cannot use window functions in WHERE clause. The reason is that WHERE clause are processed first before window functions. I would highly suggest you to read up an article, Why Can’t I use RANK() in Where Clause

To solve this, change your query to using CTE or subquery as shown below:

Subquery:

SELECT organization, name, scores
FROM (
    SELECT 
        organization, name, scores,
        RANK() OVER(PARTITION BY organization ORDER BY scores DESC) AS rnk
    FROM details
) tmp
WHERE rnk = 1