SEO

#1 (**permalink**) 02-28-2008, 08:03 PM

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.

#2 (**permalink**) 02-28-2008, 08:03 PM

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
--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

retrieving the the max occurrence of a record