How to get the first row per group?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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;