471,888 Members | 2,310 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,888 software developers and data experts.

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 6291
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
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.