C Amr Moneim C Amr Moneim - 2 months ago 5
SQL Question

How to select most frequent name from table

I have a table with lot of names like this:

MrMiroBear
MrMiroBear
BigBear
MrMiroBear
BigBear
MrMiroBear
MrMiroBear
BigBear
MrMiroBear
MrMiroBear
BigBear
MrMiroBear
BigBear
BigBear
MrMiroBear
BigBear
BigBear
MrMiroBear
BigBear


How do I select the most name frequent name in this table?
I only need the name, not the count.

I've tried this query but it gives me only count of name, not name

select Top 1 Count(*) as TopName From _Client group by ClientName

Answer

If your query works, you are not using MySQL.

The answer to your question would then be:

select Top 1 ClientName
From _Client
group by ClientName
order by count(*) desc;

In other words, you have to select the field that you want.

Note: There could be ties. If you want all top client names:

select Top (1) With Ties ClientName
From _Client
group by ClientName
order by count(*) desc;
Comments