Question 319627 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Why this query not having an output? | I want to filter out the ('student_id', student_name') Who never scored maximum or minimum

Query 1:

WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        student JOIN exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
) SELECT
        *
    FROM cte;

By Query 1, you can Check Jade is the one who never scored maximum or minimum.

Query 2:

WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        student JOIN exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
) SELECT
        DISTINCT student_id
    FROM cte
    WHERE
        score = max_score OR score = min_score;

By Query 2, you can Check Jade’s ‘student_id’ is absent

Query 3:

WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        student JOIN exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
) SELECT
    DISTINCT student_id, student_name
    FROM cte
    WHERE
        student_id NOT IN ( SELECT DISTINCT student_id
                            FROM cte
                            WHERE score = max_score OR score = min_score );

Then, Why Query 3 has no output? But when I hard code ‘student_id’ values which I got from Query 2. Then It results into correct result that I supposed having ‘Jade’ in the list.

RAW DATA:

CREATE TABLE IF NOT EXISTS Student (student_id INT, student_name VARCHAR(30));
CREATE TABLE IF NOT EXISTS Exam (exam_id INT, student_id INT, score INT);

INSERT INTO Student (student_id, student_name) VALUES (1, 'Daniel');
INSERT INTO Student (student_id, student_name) VALUES (2, 'Jade');
INSERT INTO Student (student_id, student_name) VALUES (3, 'Stella');
INSERT INTO Student (student_id, student_name) VALUES (4, 'Jonathan');
INSERT INTO Student (student_id, student_name) VALUES (5, 'Will');

INSERT INTO Exam (exam_id, student_id, score) VALUES (10, 1, 70);
INSERT INTO Exam (exam_id, student_id, score) VALUES (10, 2, 80);
INSERT INTO Exam (exam_id, student_id, score) VALUES (10, 3, 90);
INSERT INTO Exam (exam_id, student_id, score) VALUES (20, 1, 80);
INSERT INTO Exam (exam_id, student_id, score) VALUES (30, 1, 70);
INSERT INTO Exam (exam_id, student_id, score) VALUES (30, 3, 80);
INSERT INTO Exam (exam_id, student_id, score) VALUES (30, 4, 90);
INSERT INTO Exam (exam_id, student_id, score) VALUES (40, 1, 60);
INSERT INTO Exam (exam_id, student_id, score) VALUES (40, 2, 70);
INSERT INTO Exam (exam_id, student_id, score) VALUES (40, 4, 80);

Explain me why this is happening, Or If you want to show me a better approach then it is very much appreciable.

Tags:

2

Answers


  1. 0

    I rearranged your query in a second subquery to make it more organized, and now it seems to work ok.

    WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        student JOIN exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
),
cte2 as (
  SELECT
        DISTINCT student_id
    FROM cte
    WHERE
        score = max_score OR score = min_score 
)
  
  SELECT
       *
    FROM cte
    WHERE
        student_id not in (select student_id from cte2);

    fiddle example

    Login or Signup to reply.
  2. 0

    Your Query 3 selects negation of itself returning no rows. The negation should be inside of subquery in Where clause like this:

    WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        Student JOIN Exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
) SELECT
    DISTINCT student_id, student_name
    FROM cte
    WHERE
        student_id IN ( SELECT DISTINCT student_id
                            FROM cte
                            WHERE score != max_score AND score != min_score );
/*
student_id  student_name
        2   Jade
        3   Stella      */

    Since you partitioned min and max by exam_id this returns students having any of exams out of min and max values.
    If you want students that are out of min and max values of all exams then one of the options could be to make your cte flaging particular row as IS_MIN_OR_MAX (YES/NO) and then select those with no YES flags at all

    WITH cte AS (
    SELECT 
        *,
        Case When score = MAX(score) OVER (PARTITION BY exam_id) OR
                  score = MIN(score) OVER (PARTITION BY exam_id)
             Then 'YES'
        Else 'NO'
        End as IS_MIN_OR_MAX
    FROM
        Student JOIN Exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
) 

    --  M a i n    S Q L :
SELECT student_id, student_name
FROM cte
Group By student_id, student_name
Having Count(Case When IS_MIN_OR_MAX = 'YES' Then 1 End)= 0;

    /*  R e s u l t :
  student_id    student_name
           2    Jade        */

    However, your approach could work if you join the where clause’s subquery with cte by student_id like below:

    WITH cte AS (
    SELECT 
        *,
        MAX(score) OVER (PARTITION BY exam_id) AS max_score,
        MIN(score) OVER (PARTITION BY exam_id) AS min_score
    FROM
        Student JOIN Exam
        USING (student_id)
    ORDER BY
            exam_id, student_id
)
  Select * From cte c1
WHERE student_id NOT IN (SELECT DISTINCT student_id
                            FROM cte
                            WHERE student_id = c1.student_id and 
                                  (score = max_score OR score = min_score))
/*
student_id  student_name    exam_id score   max_score   min_score
        2   Jade                10    80          90          70
        2   Jade                40    70          80          60        */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search