MySQL Query with distinct on a column value

I have this table (with 100k+ rows):

room_id | emote_id | count | since
----------------------------------------
      1 |       22 |    718| 1577135778
      1 |       23 |    124| 1577135178
      1 |       24 |    842| 1577135641
      2 |       22 |    124| 1577135748
      2 |       23 |    345| 1577136441
      2 |       24 |     43| 1577543578
      3 |       22 |     94| 1572135778
      3 |       23 |   4718| 1577135641
      3 |       24 |     18| 1577134661
      4 |       22 |     78| 1577125641
      4 |       23 |    128| 1577135778
      4 |       24 |    278| 1577132577

I want to get for each emote_id the row where count is the highest for this emote_id

So for this example I’d like to get this as response:

room_id | emote_id | count | since
----------------------------------------
      1 |       22 |    718| 1577135778
      3 |       23 |   4718| 1577135641
      1 |       24 |    842| 1577135641

I’m stuck at building the query and need help. 🙁

Answer

You can use nested subquery with max() aggregation

select t1.*
  from tab t1
  join (select emote_id, max(count) as count
          from tab
         group by emote_id ) t2
    on t1.emote_id = t2.emote_id
   and t1.count = t2.count

for db version 8+ you can use window analytic functions such as dense_rank():

select room_id, emote_id, count, since
  from
  (
   select t.*, dense_rank() over (partition by emote_id order by count desc) as dr
     from tab t
  ) tt
  where tt.dr = 1  

Demo

All matching maximum values for count return through use of dense_rank() in case of tie( having equal valued count for any of emote_id ). If analytic function was row_number(), then only one row would return even if tie occurs.