SQL to find local count max per primary key-CodePudding

I have a table with PK CustomerId type. Each customer has a few types.

For each customer I want to get type which repeated the most for this customer.

I've tried to create a column "count" but I want to get the local maxs, and not a global max for the whole col.

Is there a native way to do so?

CodePudding user response：

to get type which repeated the most for this customer

You need to group by CustomerId,type. With row_number you can partition by CustomerId and order by the COUNT(type).

Try:

WITH cte AS (
   SELECT CustomerId , 
          type, 
          ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY COUNT(type) DESC ) as row_num
   FROM test
   GROUP BY CustomerId,type
) SELECT CustomerId, type
  FROM cte 
  WHERE row_num = 1 ;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8e8657dfa08ff170ed3eaf5e335b3582