GROUP BY Top 20 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

GROUP BY Top 20

Author

Topic

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-05-28 : 16:39:50

Here's a query:

DECLARE @DaysAgo int
DECLARE @Date datetime
DECLARE @Date2 datetime
SET @DaysAgo=7
SET @Date=GetDate()
SET @Date=CONVERT(VarChar(2), DatePart(mm, @Date)) + '/' + CONVERT(VarChar(2), DatePart(dd, @Date)) + '/' + CONVERT(VarChar(4), DatePart(yy, @Date))
SET @Date2=DateAdd(dd, -1*@DaysAgo, @Date)

--Top 20 Page View Counts by Path/File
SELECT TOP 20
DateDiff(dd, Track_Navigation.DateStamp, @Date) AS DaysAgo, Track_Navigation.FilePath, Track_Navigation.FileName,
COUNT(DISTINCT FK_VisitorID) AS VisitorCount, COUNT(DISTINCT FK_SessionID) AS SessionCount, COUNT(*) AS PageViewCount
FROM Track_navigation JOIN Track_Session ON Track_navigation.FK_SessionID=Track_Session.PK_SessionID
WHERE Track_Navigation.DateStamp BETWEEN @Date2 AND @Date
GROUP BY DateDiff(dd, Track_Navigation.DateStamp, @Date), Track_Navigation.filepath, Track_Navigation.filename
ORDER BY DaysAgo, PageViewCount DESC, SessionCount DESC, Track_Navigation.FilePath, Track_Navigation.FIleName

I want the top 20 viewed path/pages for each day group. Any way to do that without resorting to a loop w/ temp table or a cursor? I've tried several ways and they're either slower than loops/cursor or don't work. The above query works with @DaysAgo=1.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-29 : 05:44:21

SELECT PageViewCount,DaysAgo,Track_Navigation.FilePath
FROM (
SELECT
DateDiff(dd, Track_Navigation.DateStamp, @Date) AS DaysAgo, Track_Navigation.FilePath, Track_Navigation.FileName,
COUNT(DISTINCT FK_VisitorID) AS VisitorCount, COUNT(DISTINCT FK_SessionID) AS SessionCount, COUNT(*) AS PageViewCount
FROM Track_navigation JOIN Track_Session ON Track_navigation.FK_SessionID=Track_Session.PK_SessionID
WHERE Track_Navigation.DateStamp BETWEEN @Date2 AND @Date
GROUP BY DateDiff(dd, Track_Navigation.DateStamp, @Date), Track_Navigation.filepath, Track_Navigation.filename
ORDER BY DaysAgo, PageViewCount DESC, SessionCount DESC, Track_Navigation.FilePath, Track_Navigation.FIleName
) as tt
WHERE (PageViewCount <= 20)
ORDER BY PageViewCount

You should try using a derived table.

Edited by - YellowBug on 05/29/2002 05:45:36

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-05-30 : 15:18:42

That query only gets pages with less than 20 page views, not the top 20 most viewed pages for each time block (day). I don't think a derived table will work as it is rendered once and used rather than rendered for each iteration. A corellated subquery in the select clause won't work because I need more than 1 column.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-31 : 11:20:16

Inca,
Problems of this type are best solved with negation. Start by implementing TOP 20 as "select a row where there does not exist 20 other rows with attributes more desirable than mine".

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources