How to select max of count in PostgreSQL

Question

I have table in PostgreSQL with the following schema:

Category    |   Type 
------------+---------
A           |   0 
C           |   11 
B           |   5 
D           |   1 
D           |   0 
F           |   2   
E           |   11 
E           |   9 
.           |   . 
.           |   .

How can I select category wise maximum occurrence of type? The following give me all:

SELECT 
    category, 
    type, 
    COUNT(*)
FROM 
    table
GROUP BY 
    category, 
    type
ORDER BY 
    category, 
    count 
    DESC

My expected result is something like this:

Cat |Type |Count
--------+-------+------

A |0 |5

B |5 |30

C |2 |20

D |3 |10

That is the type with max occurrence in each category with count of that type.

Answer 1

You can use the following query:

SELECT category, type, cnt
FROM (
   SELECT category, type, cnt,
          RANK() OVER (PARTITION BY category 
                       ORDER BY cnt DESC) AS rn
   FROM (
      SELECT category, type, COUNT(type) AS cnt
      FROM mytable
      GROUP BY category, type ) t
) s
WHERE s.rn = 1

The above query uses your own query as posted in the OP and applies RANK() windowed function to it. Using RANK() we can specify all records coming from the initial query having the greatest COUNT(type) value.

Note: If there are more than one types having the maximum number of occurrences for a specific category, then all of them will be returned by the above query, as a consequence of using RANK.

Demo here

Answer 2

If I understand correctly, you can use window functions:

SELECT category, type, cnt
FROM (SELECT category, type, COUNT(*) as cnt,
             ROW_NUMBER() OVER (PARTITION BY type ORDER BY COUNT(*) DESC) as seqnum
      FROM table
      GROUP BY category, type
     ) ct
WHERE seqnum = 1;