-2

I am trying to fetch a grouped result, with only top 5 rows for each group.. Confused how to do it..

Here's the query:

SELECT O.rest_brId as BRID, O.`reason` as REASON , count(O.reason) as 
OCCURENCES 
FROM orders O 
WHERE O.status = 4 
GROUP BY BRID, REASON 
HAVING count(O.reason)

There current result is : result

What I want is that every BRID to have <= 5 rows, with TOP 5 MAX no. of OCCURENCES

How to do? Googled, but got confused.

Hyder
  • 1,163
  • 2
  • 13
  • 37

1 Answers1

1

I can't comment but would you not need to ORDER BY OCCURENCES DESC and then LIMIT 5?

shane-chris-barker
  • 76
  • 6
  • That is not how you can a top n per group.. top n per group requires a co-related subquery with a COUNT(*) or MySQL user variable to simulate a ROW_NUMBER() within a group.. MySQL 8 can use the window function ROW_NUMBER() – Raymond Nijland Dec 07 '18 at 22:05
  • then apologies, I've misunderstood the question in hand - I thought the poster was asking to limit the result to the top 5 reasons per `BRID` by the amount of occurances – shane-chris-barker Dec 07 '18 at 22:07