SQL query

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!
ankykeleAsked:
Who is Participating?
 
ThomasianCommented:
SELECT TOP 1 Country
FROM tablename
GROUP BY Country
ORDER BY COUNT(1) DESC

Open in new window

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

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

Open in new window

0
 
mayank_joshiCommented:
the query posted by Thomasian will show only one country in case of two or more countries having the highest frequency.
0
 
ankykeleAuthor Commented:
Thanks alot for the solution!!!
0
 
Pratima PharandeCommented:

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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.