How to get the first row per group?

user_6381081 0
Viewed 348

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?

window-functions group-by sql-order-by mysql sql
2 Answers

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

user_51760710

Use window functions MAX() to get the max num and FIRST_VALUE() to get the business_id of that max num:

SELECT DISTINCT 
       MAX(COUNT(*)) OVER (PARTITION BY category_id) num, 
       FIRST_VALUE(business_id) OVER (PARTITION BY category_id ORDER BY COUNT(*) DESC) business_id, 
       category_id
FROM mytable
GROUP BY business_id, category_id
ORDER BY num DESC;
user_104988280
Related