CODEWITHSUNDEEP
mysql
Oto Shavadze
Oto Shavadze

Reputation: 42823

Get value, which more times repeated in column

Given table menu, in him is column continent, for example in him is this datas

continent
----------
africa
africa
europe
europe
europe

I want get continent name, which is more times in column, that is result must be europe.

I write query, but I think my query is difficult and must be exist more ease solution for cases like this. are some ideas?

this my query

    SELECT continent FROM (
        SELECT continent, COUNT(continent) AS cnt FROM menu GROUP BY continent
    ) AS t
    WHERE cnt = (
        SELECT MAX(cnt) FROM ( 
            SELECT  COUNT(continent) AS cnt FROM menu GROUP BY continent
        ) AS t2
    )

Upvotes: 1

Views: 1309

Answers (1)

Andrea
Andrea

Reputation: 396

Try this:

SELECT continent FROM (
    SELECT continent, COUNT(continent) AS cnt
    FROM menu
    GROUP BY continent
) AS t
ORDER BY cnt DESC
LIMIT 0, 1

I think you can also do it in a single query:

SELECT continent, COUNT(continent) AS cnt
FROM menu
GROUP BY continent
ORDER BY cnt DESC
LIMIT 0, 1

Regards

UPDATE:

SELECT continent
FROM menu
GROUP BY continent
HAVING count(continent) = 
(
    SELECT COUNT(continent) AS cnt
    FROM menu
    GROUP BY continent
    ORDER BY cnt DESC
    LIMIT 0, 1
)

The internal query returns the highest number of repeated items; the value is then used in the external query applied to the HAVING operator to return only items that are repeated exactly that number of time. This works in mysql, I'm not sure with other SQL dialects.

Upvotes: 1

Related Questions