Finding the Most Common Variation

[DanCole42]

I have a table with two columns:

ProviderName
ProviderID

I have a query that groups ProviderName and ProviderID, showing how
often each name occurs for a provider:

SELECT ProviderID, ProviderNAME, Count(*) AS ProvFreq
FROM ProviderList
GROUP BY ProviderID, ProviderNAME

Now I want a second query that will show me each ID once, but only
with the most frequently occurring name. So for example, the first
query might give:

ProviderID ProviderNAME ProvFreq
010589640 HEALTH PLAN SYSTEM 1
010589640 HEALTH PLAN SYSTEMS 5
010589640 HEALTH PLAN SYSTEMS INC 2
010589640 HEALTH PLAN SYSTEMS, INC. 1
010589640 HEALTH PLUS SYSTEMS 1
010614895 ENHANCED INV TECH LLC 4
010614895 ENHANCED INVESTMENT TECH 4
010614895 ENHANCED INVESTMENT TECH LLC 1
010614895 ENHANCED INVESTMENT TECH, LLC 11
010614895 ENHANCED INVESTMENT TECHNOLOGIES 15

The second query would output:

ProviderID ProviderNAME
010589640 HEALTH PLAN SYSTEMS
010614895 ENHANCED INVESTMENT TECHNOLOGIES

How do I build the second query?

Thanks!!!

 

[John Spencer]

You can try the following.

SELECT ProviderID, ProviderName
FROM ProviderList
WHERE ProviderNAME in
(SELECT TOP 1 ProviderName
FROM ProviderList as Temp
WHERE Temp.ProviderID = ProviderList.ProviderID
GROUP BY ProviderID, ProviderName
ORDER BY Count(*) DESC)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County