retrieving the the max occurrence of a record

[pulu]

Hi,

In the datawarehouse DB (under MS commerce server 2002) a table stores
the referer domain name. Table structure is like

refererdomainid <binary>,domainInternalFlag
<0/1>,refererDomainName<varchar>

e.g.

<binary>|0|unknown
<binary>|1|google.com
<binary>|1|yahoo.com
<binary>|1|google.com
<binary>|1|google.com
<binary>|1|google.com
<binary>|1|altavista.com

my problem is to build a query (using this table only) which
refererDomainName has the max occurrence and how many times. As in the
table above it is google.com and 4 times.

Can anyone help me.
Thanks in advance.

[David Portas]

Here are two alternatives:

SELECT refererdomainname, COUNT(*)
FROM SomeTable
GROUP BY refererdomainname
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM SomeTable
GROUP BY refererdomainname)

SELECT TOP 1 WITH TIES
refererdomainname, COUNT(*)
FROM SomeTable
GROUP BY refererdomainname
ORDER BY COUNT(*) DESC

--
David Portas
SQL Server MVP
--