In the following code snippet, how is MAX choosing the MAX number of rows of each occupation when it is applied over Name field?

WITH RankedOccupations AS (
    SELECT
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum
    FROM OCCUPATIONS
)
SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
FROM RankedOccupations
GROUP BY RowNum;

This is the solution of this problem.

In the following code snippet, how is MAX choosing the MAX number of rows of each occupation when it is applied over Name field?

WITH RankedOccupations AS (
    SELECT
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum
    FROM OCCUPATIONS
)
SELECT
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
FROM RankedOccupations
GROUP BY RowNum;

This is the solution of this problem.

• sql
• mysql

• 1 What is the actual problem statement or goal here? – Tim Biegeleisen Commented Jan 20 at 10:31
• I think it used for pivoting along that column – Nanomachines Son Commented Jan 20 at 11:16
• A minimal reproducible example is a great start when asking for SQL assistance. – jarlh Commented Jan 20 at 12:32
• You're using case expressions, not statements. And tables have columns, not fields. – jarlh Commented Jan 20 at 12:33

2 Answers 2

MAX is not determining the number of rows per occupation. ROW_NUMBER ... AS rownum numbers those rows and GROUP BY rownum ensures to get as many result rows.

Example:

We see here that we want one result row for rownum 1 and one for rownum 2. GROUP BY rownum gives us one result row per rownum.

MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) is short for MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END). So, for each original row in a group we'll either get a name or null. And MAX will pick one of these values. As there is a maximum of one name per profession per rownum, we'll either get one name for the desired profession and NULLs for all other professions, so MAX will give us the name, or we'll only get NULLs, the maximum of which is NULL.

In detail:

• for rownum 1 we'll get for Doctors: Alex and NULL, and the maximum of these is Alex.
• for rownum 1 we'll get for Professors: NULL and Judy, and the maximum of these is Judy.
• for rownum 2 we'll get for Doctors: Ben and NULL, and the maximum of these is Ben.
• for rownum 2 we'll get for Professors: NULL and NULL, and the maximum of these is NULL.

Final result:

The solution of a linked problem your question is based on ...

This is the solution of this problem.

... could be like this ...

--  S a m p l e    D a t a :
Create Table OCUPATIONS(A_NAME Text, OCUPATION Text);

Insert Into OCUPATIONS
Values ('Samantha', 'Doctor'), 
       ('Julia', 'Actor'), 
       ('Maria', 'Actor'), 
       ('Meera', 'Singer'), 
       ('Ashely', 'Professor'), 
       ('Ketty', 'Professor'), 
       ('Christeen', 'Professor'),
       ('Jane', 'Actor'),
       ('Jenny', 'Doctor'), 
       ('Priya', 'Singer');

... in inner query - use Case expressions to create the four (OCUPATION) columns and Row_Number() Over() analytic function to create the column (RN) that would be used as Group By column for outer query's Max() aggregation

SELECT    Max(o.DOCTOR) "DOCTOR", Max(o.PROFESSOR) "PROFESSOR", 
          Max(o.SINGER) "SINGER", Max(o.ACTOR) "ACTOR"
FROM    ( Select    Case When OCUPATION = 'Doctor' Then A_NAME End "DOCTOR",
                    Case When OCUPATION = 'Professor' Then A_NAME End "PROFESSOR",
                    Case When OCUPATION = 'Singer' Then A_NAME End "SINGER",
                    Case When OCUPATION = 'Actor' Then A_NAME End "ACTOR",
                    Row_Number() Over(Partition By OCUPATION Order By A_NAME) "RN"
          From      OCUPATIONS
        ) o
GROUP BY  o.RN
ORDER BY  o.RN

fiddle