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

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

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

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;

SELECT
    category,
    type,
    COUNT(*)
FROM
    table
GROUP BY
    category,
    type
HAVING
    COUNT(*) = (SELECT MAX(C) FROM (SELECT COUNT(*) AS C FROM A GROUP BY A) AS Q)

COUNT(*) = (SELECT MAX(COUNT(*)) FROM table GROUP BY category,type)

COUNT(*) = (SELECT MAX(C) FROM (SELECT COUNT(*) AS C FROM A GROUP BY A) AS Q)

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