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:

two

one
two
two
three


7 Answers
7

SELECT `column`,
COUNT(`column`) AS `value_occurrence`
FROM `my_table`
GROUP BY `column`
ORDER BY `value_occurrence` DESC
LIMIT 1;


Replace column and my_table. Increase 1 if you want to see the N most common values of the column.

column

my_table

1

N

SELECT * FROM my_table GROUP BY value ORDER BY count(*) DESC;

WHERE 'value_occurrence' = 1

HAVING

WHERE

Try something like:

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


COUNT(*)

ORDER BY

GROUP BY

HAVING

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

tblperson

city

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.

nor

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
..
..


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;


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

SalesLT.Customer

CompanyName

AggCompanyName

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


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


ROWNUM

LIMIT 1

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.