Selecting rows with highest count value

15 Dec 2005 9:06 PM

Mark Williams

I have a table that stores IIS access logs. I run

SELECT username, target, COUNT(*) as "hits"
FROM weblog
GROUP BY username, target

to show how many times each user has hit each target. I would like to find
out what each users' most popular target is. In other words, for each user, I
want to find out which target had the highest "hit" value. I tried using the
HAVING clause to no effect. Any help would be greatly appreciated.

15 Dec 2005 9:11 PM

JI

SELECT username, target, COUNT(1) as "hits"
FROM weblog
GROUP BY username, target
order by count(1) desc

or if you want the top 10

SELECT top 10 username, target, COUNT(1) as "hits"
FROM weblog
GROUP BY username, target
order by count(1) desc

Show quote

"Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
news:C7DB799B-BF71-4A57-AC3F-9E4854A7DF81@microsoft.com...
>I have a table that stores IIS access logs. I run
>
> SELECT username, target, COUNT(*) as "hits"
> FROM weblog
> GROUP BY username, target
>
> to show how many times each user has hit each target. I would like to find
> out what each users' most popular target is. In other words, for each
> user, I
> want to find out which target had the highest "hit" value. I tried using
> the
> HAVING clause to no effect. Any help would be greatly appreciated.
>
>

15 Dec 2005 9:52 PM

Mark Williams

The result set of these queries will contain duplicates of the username.

Think of the record set produced by the query

SELECT username, target, COUNT(*) as "hits"
FROM weblog
GROUP BY username, target

For each username in that record set, there will be one target that has a
value of "hit" that is bigger than all the other targets for that username. I
want to get that row for each username in the recordset.

Show quote

"JI" wrote:

> SELECT username, target, COUNT(1) as "hits"
> FROM weblog
> GROUP BY username, target
> order by count(1) desc
>
> or if you want the top 10
>
> SELECT top 10 username, target, COUNT(1) as "hits"
> FROM weblog
> GROUP BY username, target
> order by count(1) desc
>
> "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
> news:C7DB799B-BF71-4A57-AC3F-9E4854A7DF81@microsoft.com...
> >I have a table that stores IIS access logs. I run
> >
> > SELECT username, target, COUNT(*) as "hits"
> > FROM weblog
> > GROUP BY username, target
> >
> > to show how many times each user has hit each target. I would like to find
> > out what each users' most popular target is. In other words, for each
> > user, I
> > want to find out which target had the highest "hit" value. I tried using
> > the
> > HAVING clause to no effect. Any help would be greatly appreciated.
> >
> >
>
>
>

16 Dec 2005 4:25 AM

Uri Dimant

Mark
Can you show us DDL+ sample data + expected result?

I did some testing on Northwind database

---Getting a last OrderDate for each customer

SELECT CustomerID,OrderDate FROM Orders
WHERE OrderDate=(SELECT MAX(OrderDate) FROM Orders O
WHERE O.CustomerID=Orders.CustomerID)
ORDER BY CustomerId

Show quote

"Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
news:9813F06D-3F07-46D9-A165-8C287E07832F@microsoft.com...
> The result set of these queries will contain duplicates of the username.
>
> Think of the record set produced by the query
>
> SELECT username, target, COUNT(*) as "hits"
> FROM weblog
> GROUP BY username, target
>
> For each username in that record set, there will be one target that has a
> value of "hit" that is bigger than all the other targets for that
> username. I
> want to get that row for each username in the recordset.
>
> "JI" wrote:
>
>> SELECT username, target, COUNT(1) as "hits"
>> FROM weblog
>> GROUP BY username, target
>> order by count(1) desc
>>
>> or if you want the top 10
>>
>> SELECT top 10 username, target, COUNT(1) as "hits"
>> FROM weblog
>> GROUP BY username, target
>> order by count(1) desc
>>
>> "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
>> news:C7DB799B-BF71-4A57-AC3F-9E4854A7DF81@microsoft.com...
>> >I have a table that stores IIS access logs. I run
>> >
>> > SELECT username, target, COUNT(*) as "hits"
>> > FROM weblog
>> > GROUP BY username, target
>> >
>> > to show how many times each user has hit each target. I would like to
>> > find
>> > out what each users' most popular target is. In other words, for each
>> > user, I
>> > want to find out which target had the highest "hit" value. I tried
>> > using
>> > the
>> > HAVING clause to no effect. Any help would be greatly appreciated.
>> >
>> >
>>
>>
>>

16 Dec 2005 4:28 PM

Mark Williams

Here is the DDL for the table that I bulk insert the log files into:

CREATE TABLE [weblogchar] (
    [clientip] [varchar] (20) ,
    [username] [varchar] (50) ,
    [date] [varchar] (20) ,
    [time] [varchar] (20) ,
    [instance] [varchar] (50) ,
    [servername] [varchar] (50) ,
    [serverip] [varchar] (20) ,
    [timetaken] [int] NULL ,
    [cbytessent] [int] NULL ,
    [sbytessent] [int] NULL ,
    [servstatus] [int] NULL ,
    [winstatus] [bigint] NULL ,
    [reqtype] [varchar] (10) ,
    [target] [varchar] (500) ,
    [params] [varchar] (1024)
) ON [PRIMARY]
GO

I have a view that concatenates the date and time fields, and converts the
result into a datetime data type.

CREATE VIEW weblog AS (
SELECT clientip, username, CAST([date] + [time] AS datetime) AS [time],
instance, servername, serverip, timetaken, cbytessent, sbytessent,
servstatus, winstatus, reqtype, target, params
FROM weblogchar
)

Run the query below, which shows how many times each user has hit each page
on the web site:

SELECT username, target, COUNT(*)
FROM weblog
GROUP BY username, target

26519 rows

Sample of result:

username, target, hit count

chicks                    /C14/LDI Documentation/default.aspx
118
chicks                    /default.aspx
124
chicks                    /_vti_bin/_vti_aut/author.dll
147
chicks                    /C17/SCLDI/default.aspx
151
chicks                    /_vti_bin/owssvr.dll
3208
ehorton                   /default.aspx
171
ehorton                   /_vti_bin/shtml.dll/_vti_rpc
1161
ehorton                   /_vti_bin/owssvr.dll
1928
ehorton                   /_vti_bin/_vti_aut/author.dll
2322

What I would like is a query that would, for each user, give the target that
has the highest value for hit count. Using this sample data, the result of
that query would be:

chicks                    /_vti_bin/owssvr.dll
ehorton                   /_vti_bin/_vti_aut/author.dll

Show quote

"Uri Dimant" wrote:

> Mark
> Can you show us DDL+ sample data + expected result?
>
> I did some testing on Northwind database
>
> ---Getting a last OrderDate for each customer
>
> SELECT CustomerID,OrderDate FROM Orders
> WHERE OrderDate=(SELECT MAX(OrderDate) FROM Orders O
> WHERE O.CustomerID=Orders.CustomerID)
> ORDER BY CustomerId
>
>
> "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
> news:9813F06D-3F07-46D9-A165-8C287E07832F@microsoft.com...
> > The result set of these queries will contain duplicates of the username.
> >
> > Think of the record set produced by the query
> >
> > SELECT username, target, COUNT(*) as "hits"
> > FROM weblog
> > GROUP BY username, target
> >
> > For each username in that record set, there will be one target that has a
> > value of "hit" that is bigger than all the other targets for that
> > username. I
> > want to get that row for each username in the recordset.
> >
> > "JI" wrote:
> >
> >> SELECT username, target, COUNT(1) as "hits"
> >> FROM weblog
> >> GROUP BY username, target
> >> order by count(1) desc
> >>
> >> or if you want the top 10
> >>
> >> SELECT top 10 username, target, COUNT(1) as "hits"
> >> FROM weblog
> >> GROUP BY username, target
> >> order by count(1) desc
> >>
> >> "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
> >> news:C7DB799B-BF71-4A57-AC3F-9E4854A7DF81@microsoft.com...
> >> >I have a table that stores IIS access logs. I run
> >> >
> >> > SELECT username, target, COUNT(*) as "hits"
> >> > FROM weblog
> >> > GROUP BY username, target
> >> >
> >> > to show how many times each user has hit each target. I would like to
> >> > find
> >> > out what each users' most popular target is. In other words, for each
> >> > user, I
> >> > want to find out which target had the highest "hit" value. I tried
> >> > using
> >> > the
> >> > HAVING clause to no effect. Any help would be greatly appreciated.
> >> >
> >> >
> >>
> >>
> >>
>
>
>

16 Dec 2005 7:04 PM

Mark Williams

A coworker came up with this solution. I created a view

CREATE VIEW UserTargetHits
AS
(SELECT username, target, COUNT(*) as "hits"
FROM weblogchar
GROUP BY username, target
)

Using this view, the following query produces the results I was looking for

SELECT DISTINCT username, (SELECT TOP 1 target FROM UserTargetHits WHERE
username=a.username AND hits=(SELECT MAX(hits) FROM UserTargetHits WHERE
username=a.username))
FROM UserTargetHits a

Show quote

"Mark Williams" wrote:

> Here is the DDL for the table that I bulk insert the log files into:
>
> CREATE TABLE [weblogchar] (
>     [clientip] [varchar] (20) ,
>     [username] [varchar] (50) ,
>     [date] [varchar] (20) ,
>     [time] [varchar] (20) ,
>     [instance] [varchar] (50) ,
>     [servername] [varchar] (50) ,
>     [serverip] [varchar] (20) ,
>     [timetaken] [int] NULL ,
>     [cbytessent] [int] NULL ,
>     [sbytessent] [int] NULL ,
>     [servstatus] [int] NULL ,
>     [winstatus] [bigint] NULL ,
>     [reqtype] [varchar] (10) ,
>     [target] [varchar] (500) ,
>     [params] [varchar] (1024)
> ) ON [PRIMARY]
> GO
>
> I have a view that concatenates the date and time fields, and converts the
> result into a datetime data type.
>
> CREATE VIEW weblog AS (
>   SELECT clientip, username, CAST([date] + [time] AS datetime) AS [time],
>   instance, servername, serverip, timetaken, cbytessent, sbytessent,
>   servstatus, winstatus, reqtype, target, params
>   FROM weblogchar
> )
>
> Run the query below, which shows how many times each user has hit each page
> on the web site:
>
>
> SELECT username, target, COUNT(*)
> FROM weblog
> GROUP BY username, target
>
> 26519 rows
>
> Sample of result:
>
> username, target, hit count
>
> chicks                    /C14/LDI Documentation/default.aspx
> 118
> chicks                    /default.aspx
> 124
> chicks                    /_vti_bin/_vti_aut/author.dll
> 147
> chicks                    /C17/SCLDI/default.aspx
> 151
> chicks                    /_vti_bin/owssvr.dll
> 3208
> ehorton                   /default.aspx
> 171
> ehorton                   /_vti_bin/shtml.dll/_vti_rpc
> 1161
> ehorton                   /_vti_bin/owssvr.dll
> 1928
> ehorton                   /_vti_bin/_vti_aut/author.dll
> 2322
>
> What I would like is a query that would, for each user, give the target that
> has the highest value for hit count. Using this sample data, the result of
> that query would be:
>
> chicks                    /_vti_bin/owssvr.dll
> ehorton                   /_vti_bin/_vti_aut/author.dll
>
>
>
> "Uri Dimant" wrote:
>
> > Mark
> > Can you show us DDL+ sample data + expected result?
> >
> > I did some testing on Northwind database
> >
> > ---Getting a last OrderDate for each customer
> >
> > SELECT CustomerID,OrderDate FROM Orders
> > WHERE OrderDate=(SELECT MAX(OrderDate) FROM Orders O
> > WHERE O.CustomerID=Orders.CustomerID)
> > ORDER BY CustomerId
> >
> >
> > "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
> > news:9813F06D-3F07-46D9-A165-8C287E07832F@microsoft.com...
> > > The result set of these queries will contain duplicates of the username.
> > >
> > > Think of the record set produced by the query
> > >
> > > SELECT username, target, COUNT(*) as "hits"
> > > FROM weblog
> > > GROUP BY username, target
> > >
> > > For each username in that record set, there will be one target that has a
> > > value of "hit" that is bigger than all the other targets for that
> > > username. I
> > > want to get that row for each username in the recordset.
> > >
> > > "JI" wrote:
> > >
> > >> SELECT username, target, COUNT(1) as "hits"
> > >> FROM weblog
> > >> GROUP BY username, target
> > >> order by count(1) desc
> > >>
> > >> or if you want the top 10
> > >>
> > >> SELECT top 10 username, target, COUNT(1) as "hits"
> > >> FROM weblog
> > >> GROUP BY username, target
> > >> order by count(1) desc
> > >>
> > >> "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message
> > >> news:C7DB799B-BF71-4A57-AC3F-9E4854A7DF81@microsoft.com...
> > >> >I have a table that stores IIS access logs. I run
> > >> >
> > >> > SELECT username, target, COUNT(*) as "hits"
> > >> > FROM weblog
> > >> > GROUP BY username, target
> > >> >
> > >> > to show how many times each user has hit each target. I would like to
> > >> > find
> > >> > out what each users' most popular target is. In other words, for each
> > >> > user, I
> > >> > want to find out which target had the highest "hit" value. I tried
> > >> > using
> > >> > the
> > >> > HAVING clause to no effect. Any help would be greatly appreciated.
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >