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

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

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search