By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with tab(room_id , emote_id , count , since) as
(
select 1 , 22 , 718, 1577135778 union all
select 1 , 23 , 124, 1577135178 union all
select 1 , 24 , 842, 1577135641 union all
select 2 , 22 , 124, 1577135748 union all
select 2 , 23 , 345, 1577136441 union all
select 2 , 24 , 43, 1577543578 union all
select 3 , 22 , 94, 1572135778 union all
select 3 , 23 , 4718, 1577135641 union all
select 3 , 24 , 18, 1577134661 union all
select 4 , 22 , 78, 1577125641 union all
select 4 , 23 , 128, 1577135778 union all
select 4 , 24 , 278, 1577132577
)
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
| room_id | emote_id | count | since |
|---|---|---|---|
| 1 | 22 | 718 | 1577135778 |
| 1 | 24 | 842 | 1577135641 |
| 3 | 23 | 4718 | 1577135641 |
with tab(room_id , emote_id , count , since) as
(
select 1 , 22 , 718, 1577135778 union all
select 1 , 23 , 124, 1577135178 union all
select 1 , 24 , 842, 1577135641 union all
select 2 , 22 , 124, 1577135748 union all
select 2 , 23 , 345, 1577136441 union all
select 2 , 24 , 43, 1577543578 union all
select 3 , 22 , 94, 1572135778 union all
select 3 , 23 , 4718, 1577135641 union all
select 3 , 24 , 18, 1577134661 union all
select 4 , 22 , 78, 1577125641 union all
select 4 , 23 , 128, 1577135778 union all
select 4 , 24 , 278, 1577132577
)
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
| room_id | emote_id | count | since |
|---|---|---|---|
| 1 | 22 | 718 | 1577135778 |
| 3 | 23 | 4718 | 1577135641 |
| 1 | 24 | 842 | 1577135641 |