How to get the first row per group?

I have a query like this:

select count(1) num, business_id, category_id
from mytable
group by business_id, category_id
order by num desc

The result looks like this:

// res
+-----+-------------+-------------+
| num | business_id | category_id |
+-----+-------------+-------------+
| 22  | 5543        | 8           |
| 19  | 4352        | 8           |
| 13  | 3242        | 11          |
| 10  | 2132        | 11          |
| 7   | 6832        | 8           |
+-----+-------------+-------------+

Now I want to get the first row for each category_id. So it must be the biggest num and its business_id. So the expected result would be:

// expected res
+-----+-------------+-------------+
| num | business_id | category_id |
+-----+-------------+-------------+
| 22  | 5543        | 8           |
| 13  | 3242        | 11          |
+-----+-------------+-------------+

How can I do that?

Answer

if your MySQL version support ROW_NUMBER + window function, you can try to use ROW_NUMBER to get the biggest num by category_id

Query #1

SELECT num,business_id,category_id
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY num desc) rn
    FROM (
        select count(1) num, business_id, category_id
        from mytable
        group by business_id, category_id
    ) t1
) t1
WHERE rn = 1

num	business_id	category_id
22	5543	8
13	3242	11

---

View on DB Fiddle