Search code examples
sqlpostgresqlcountmaxhaving

Find the most frequent seen column_name using max

I have a table named food:

CREATE TABLE food
(
    name varchar(30) primary key,
    type varchar(30)
);

I want to find the type that appears the most time in this table.

I can find this using

SELECT type
FROM food
GROUP BY type
ORDER BY count(type) DESC
LIMIT 1

But limit 1 is not in included in the standards. How can I find the same result using MAX? For example I want something like

SELECT type
FROM food
GROUP BY type
HAVING count(type) = MAX(count(type));

HAVING count(type) = MAX(count(type)); isn't allowed.

Solution

  • May not be efficient

    SELECT type
FROM food
GROUP BY type
HAVING COUNT(type) =
(
    SELECT MAX(counting) FROM
    (
        SELECT COUNT(type) AS counting
        FROM food
        GROUP BY type
    ) t
)