469,572 Members | 1,343 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

retrieving the the max occurrence of a record

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.
Jul 20 '05 #1
1 6221
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
--
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by tangus via DotNetMonster.com | last post: by
11 posts views Thread by Nemisis | last post: by
7 posts views Thread by rfinch | last post: by
15 posts views Thread by gunnar.sigurjonsson | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.