Find most frequent value in SQL column

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try something like   SELECT        column      FROM      your table      GROUP BY  column      ORDER BY COUNT    DESC     LIMIT    1

User · Answer

One way I like to use is   select  COUNT  as VAR1 from Table Name  group by   order by VAR1 desc  limit 1

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Find most frequent value in SQL column

Examples related to mysql

Examples related to sql