[SQL] Select column, max(count(column))

In SQL, to know which row id has the MAX(COUNT(*)) in a table, you would like to:

SELECT id, MAX(COUNT(*))

FROM table

WHERE [conditions]

But this will not work on its own, instead you should:

SELECT query1.*

FROM( SELECT t.id,
        COUNT(*) AS count1
        FROM table t
        GROUP BY t.id) [AS] query1,
(SELECT MAX(query2.count2) AS max_count
FROM (SELECT t1.id,
       COUNT(*) AS count2
       FROM table t1
       GROUP BY t1.id) [AS]  query2) [AS] query3
WHERE query1.count1=query3.max_count

Do NOT use t.* or t1.* inside a COUNT(column).

Also, note that Oracle complains if you assign a query alias using the keyword AS, so either omit it or declare the subquery alias before by using:

WITH [alias] AS (SELECT column FROM table WHERE [conditions])