Selecting max value while group by two columns with order on another column

Question

I have table with 4 rows id season_id market elements I would like to select for each same season_id, market that hold max result and if max results are the same then max based on elements only where elements are higher than 9.

id  | season_id | market | result    | elements
 1  |    20     |   fh   |    75     |   20
 2  |    20     |   fh   |    75     |   22
 3  |    20     |   SH   |    81     |   18
 4  |    20     |   SH   |    75     |   20
 5  |    21     |   fh   |    90     |   14
 6  |    21     |   fh   |    86     |   16
 7  |    21     |   SH   |    90     |   18
 8  |    21     |   SH   |    91     |   2

I would like to get

   id  | season_id | market | result    | elements
    2  |    20     |   fh   |    75     |   22
    3  |    20     |   SH   |    81     |   18
    5  |    21     |   fh   |    90     |   14
    7  |    21     |   SH   |    90     |   18

I've tried

  SELECT a.* FROM results a INNER JOIN (SELECT id, market, MAX(result) as perc FROM 
  results where elements>9 group by market ) group ON a.market = group.market and 
  a.result = group.perc group by market

But it doesn't select all the markets and I'm not sure how to add selection by number of elements to that

Answer 1

You seem to want one result per season_id/market pair. I'm not 100% sure what the limit on elements > 9 is supposed to be doing, but I think it is an overall filter.

To get the rows with the maximum result and elements for each season and market, use row_number():

select t.*
from (select t.*,
             row_number() over (partition by season_id, market order by result desc, elements desc) as seqnum
      from t
      where elements > 9
     ) t
where seqnum = 1;

Here is a db<>fiddle.

In older versions of MySQL (or even more recent versions), you can use a correlated subquery:

select t.*
from t
where t.id = (select t2.id
              from t t2
              where t2.season_id = t.season_id and t2.market = t.market and
                    t2.elements > 9
              order by t2.result desc, t2.elements desc
              limit 1
             );