MySQL Selecting table that has the most columns?

Question

select t.table_name from information_schema.tables t
left join information_schema.columns c
on c.table_name = t.table_name
and c.table_schema = t.table_schema
where t.table_schema = 'test'
and count(c.column_name) = max(count(c.column_name))

I am trying to select the table that has the highest amount of columns from the database "test." However, I am unable to do this. I have tried to select count(c.column_name) as well, but instead of returning the column count for each table, it just returns the maximum column count.

How can I select the table that has the most columns?

Answer 1

Try grouping by table name. Something like:

SELECT TABLE_NAME, COUNT(*) as c
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
ORDER BY c desc
LIMIT 1;

Fiddle