[mysql] Find most frequent value in SQL column

How can I find the most frequent value in a given column in an SQL table?

For example, for this table it should return two since it is the most frequent value:

one
two
two
three

Below query seems to work good for me in SQL Server database:

select column, COUNT(column) AS MOST_FREQUENT
from TABLE_NAME
GROUP BY column
ORDER BY COUNT(column) DESC

Result:

column          MOST_FREQUENT
item1           highest count
item2           second highest 
item3           third higest
..
..

If you have an ID column and you want to find most repetitive category from another column for each ID then you can use below query,

Table:

Query:

SELECT ID, CATEGORY, COUNT(*) AS FREQ
FROM TABLE
GROUP BY 1,2
QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY FREQ DESC) = 1;

Result:

One way I like to use is:

select ,COUNT()as VAR1 from Table_Name

group by

order by VAR1 desc

limit 1

Try something like:

SELECT       `column`
    FROM     `your_table`
    GROUP BY `column`
    ORDER BY COUNT(*) DESC
    LIMIT    1;

If you can't use LIMIT or LIMIT is not an option for your query tool. You can use "ROWNUM" instead, but you will need a sub query:

SELECT FIELD_1, ALIAS1
FROM(SELECT FIELD_1, COUNT(FIELD_1) ALIAS1
    FROM TABLENAME
    GROUP BY FIELD_1
    ORDER BY COUNT(FIELD_1) DESC)
WHERE ROWNUM = 1

For use with SQL Server.

As there is no limit command support in that.

Yo can use the top 1 command to find the maximum occurring value in the particular column in this case (value)

SELECT top1 
    `value`,
    COUNT(`value`) AS `value_occurrence` 
FROM     
    `my_table`
GROUP BY 
    `value`
ORDER BY 
    `value_occurrence` DESC;

Let us consider table name as tblperson and column name as city. I want to retrieve the most repeated city from the city column:

 select city,count(*) as nor from tblperson
        group by city
          having count(*) =(select max(nor) from 
            (select city,count(*) as nor from tblperson group by city) tblperson)

Here nor is an alias name.

Assuming Table is 'SalesLT.Customer' and the Column you are trying to figure out is 'CompanyName' and AggCompanyName is an Alias.

Select CompanyName, Count(CompanyName) as AggCompanyName from SalesLT.Customer
group by CompanyName
Order By Count(CompanyName) Desc;