Rank() function with WHERE clause display nothing MYSQL-CodePudding

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?

CodePudding user response：

SELECT DISTINCT
       organization,
       FIRST_VALUE(name) OVER (PARTITION BY organization ORDER BY scores DESC) name,
       MAX(scores) OVER (PARTITION BY organization) scores
FROM details

CodePudding user response：

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

CodePudding user response：

This is another way using CTE.

WITH tmp AS 
(
  SELECT DISTINCT organization, `name`, scores,
  RANK() OVER (PARTITION BY organization ORDER BY scores DESC) `rank`
  FROM details
)
SELECT organization, `name`, scores FROM tmp WHERE `rank` = 1;

DB Fiddle

Note : rank is a reserve word so you must be careful in using it, always embed it in backticks.

Check MySQL Common Table Expression for more details about CTE.

In MySQL, every statement or query produces a temporary result or relation. A common table expression or CTE is used to name those temporary results set that exist within the execution scope of that particular statement