<div>
<pre><code id="code-20-35229688-1">with cte as
(
select country,count(ID) as cnt from tblcounty group by country )

select * from cte where cnt=(Select max(cnt) from cte);</code></pre>
</div>


with cte as
(
select country,count(ID) as cnt from tblcounty group by country )

select * from cte where cnt=(Select max(cnt) from cte);

<div>
the query posted by Thomasian will show only one country in case of two or more countries having the highest frequency.
</div>


the query posted by Thomasian will show only one country in case of two or more countries having the highest frequency.

<div>
Thanks alot for the solution!!!
</div>


<div>
<br />
return the highest frequency item in the country column: US<br />
Meabns required only on record<br />
<br />
SELECT Top 1 Country<br />
FROM tablename<br />
GROUP BY Country<br />
ORDER BY COUNT(ID) DESC
</div>



return the highest frequency item in the country column: US
Meabns required only on record

SELECT Top 1 Country
FROM tablename
GROUP BY Country
ORDER BY COUNT(ID) DESC

<div>
<div class="content wysiwyg-content">
Hello,<br />
I am trying to write a SQL query that will return the item in a column which occurs the most. &nbsp;My data looks like this:<br />
<br />
ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Country<br />
1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; US<br />
2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UK<br />
3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Canada<br />
4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; US<br />
<br />
I would like my query to return the highest frequency item in the country column: US<br />
<br />
Thanks in advance!
</div>
</div>


Hello,
I am trying to write a SQL query that will return the item in a column which occurs the most.  My data looks like this:

ID          Country
1               US
2               UK
3               Canada
4               US

I would like my query to return the highest frequency item in the country column: US

Thanks in advance!

SQL query

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.