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

Retrieving all user rights in SQL 2000/2005

How to retrieve all users (local and domain) in SQL and display there
rights in roles, SUID, database, etc.?Hello,
Take a look into sp_helplogins and sp_helprotect system stored procedures
in books online.
Thanks
Hari
<paul.leistra@.gmail.com> wrote in message
news:1175670638.835844.157430@.p77g2000hsh.googlegroups.com...

> How to retrieve all users (local and domain) in SQL and display there
> rights in roles, SUID, database, etc.?
>

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.
to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.
|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.
|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:
> Is there a way to pull back the domain name that a server is in from within
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.

Retrieve Domain Name

Is there a way to pull back the domain name that a server is in from within
SQL?
ie @.@.servername returns the server/instance, something similar for the
domain is what I am looking for.to capture the start time, end time, application name, client host name, NT
user name and the domain name.
DECLARE @.TraceID int, @.DB_ID int
EXEC CreateTrace
'C:\My SQL Traces\ProceduresCalledInMSDB',
@.OutputTraceID = @.TraceID OUT
EXEC AddEvent
@.TraceID,
'SP:Completed',
'TextData, StartTime, EndTime, ApplicationName, ClientHostName, NTUserName,
NTDomainName, DatabaseID'
SET @.DB_ID = DB_ID('msdb')
EXEC AddFilter
@.TraceID,
'DatabaseID',
@.DB_ID
EXEC StartTrace 1
GO
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||Sorry all, this is not within a trace but just at a sql analyzer window or
within a stored proc?
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.|||I don't think there is any direct statement in SQL to get the domain name.
I think these statement will give the domain information for a login or
associated to the server:
xp_loginconfig
xp_enumgroups
xp_logininfo
"Thom" wrote:

> Is there a way to pull back the domain name that a server is in from withi
n
> SQL?
> ie @.@.servername returns the server/instance, something similar for the
> domain is what I am looking for.