Better way to select max values in multiple-column group?

Ricardo Francois 0
Viewed 25

Simplified the following question I got from a coding challenge...

I have a table grades like:

year   sex   person   mark
2000   M     Mark     70
2010   F     Alyssa   23
2020   M     Robert   54

I want to select the people per year for both sexes that have the highest marks.

My Attempt:

   SELECT
        year,
        MAX(CASE
            WHEN sex = ‘F’ THEN person
            ELSE ‘’ END) AS person_f,
        MAX(CASE
            WHEN sex = ‘M’ THEN person
            ELSE ‘’ END) AS person_m
    FROM (
        SELECT
            year,
            sex,
            person,
            **mark
        FROM grades
        WHERE mark IN (
            SELECT MAX(mark) AS mark
            FROM grades
            GROUP BY year, sex)
        **) AS t
    WHERE x = 1
    GROUP BY 1
    ORDER BY 1

I modified everything within the ** ** but the rest of the code was pre-populated. The code seemed right to me, but somehow only passed 2/4 test cases, and there were no tiebreaker records.

Also, I omitted the WHERE x = 1 line, but the correct solution apparently needs that. (yes, x isn't a column in any table)

Is there a more elegant/efficient way to solve this? Can't seem to figure it out, and it's really bugging me.

group-by subquery mysql database sql
3 Answers

First you need to use single quotes for Strings

The Problem of your query, is the subquery for your marks, you select a bunch of highest marks without associating them to the year, and gender

MySql allows you to IN clause with multiple columns.

   SELECT
        year,
        MAX(CASE
            WHEN sex = 'F' THEN person
            ELSE '' END) AS person_f,
        MAX(CASE
            WHEN sex = 'M' THEN person
            ELSE '' END) AS person_m
    FROM (
        SELECT
            year,
            sex,
            person,
            mark
        FROM grades
        WHERE (year,sex,mark) IN (
            SELECT year, sex,MAX(mark) AS mark
            FROM grades
            GROUP BY year, sex)
        ) AS t
    GROUP BY 1
    ORDER BY 1


| year | person\_f | person\_m |
|-----:|:----------|:----------|
| 2000 |           | Mark      |
| 2010 | Alyssa    |           |
| 2020 |           | Robert    |

fiddle

nbk0

I believe this approach incorporates the WHERE x = 1 clause as well.

   SELECT
        year,
        MAX(CASE
            WHEN sex = 'F' THEN person
            ELSE '' END) AS person_f,
        MAX(CASE
            WHEN sex = 'M' THEN person
            ELSE '' END) AS person_m
    FROM (
        SELECT
            year,
            sex,
            person,
            RANK() OVER (PARTITION BY year, sex ORDER BY mark DESC) AS x
        FROM grades)
    WHERE x = 1
    GROUP BY 1
    ORDER BY 1
Ricardo Francois0

You can use rank. I added info to the table so you could see what happens in different scenarios including a tie.

select  year
       ,sex 
       ,person  
       ,mark
from   (
        select *
               ,rank() over(partition by year, sex order by mark desc) as rnk
        from   t
       ) t
where  rnk = 1
order  by year, sex
year sex person mark
2000 F Alyssa 23
2000 M Mark 70
2000 M Danny 70
2010 F Alma 100
2010 M Dudu 47
2020 F Noga 98
2020 M Moshe 56

Fiddle

DannySlor0
Related