Problem limiting rows returned from aggregated data

[mkal]

I have a query that returns some aggreagated data about users who visit a website. We track who they are (ID), what project they belong in, name of the page they visit and how many times they hit a given page.
Table looks like this
ID, Project, PageID .... more columns but I only need these.

Query looks like this:
Select ID, Project, PageID, Count(PageID)
From vw_SER
Group By ID, Project, PageID
Order By ID, Count(PageID) desc

An example of the data would look like this
ID Project PageID NumberOfTimesViewed
1 22 Home 25
1 22 Products 20
1 22 about us 15
1 22 tips 14
1 22 maps 12
1 22 services 10
1 22 contact us 8
1 22 specials 7
1 22 requests 5
.
..
...
34 55 Products 45
34 55 Home 19
.
..
... more of same

The id number and project values will change - each visitor has a unique id and will belong in a singel or multiple projects.

What I want is the top five pages per ID, per project. Is there a way to limit the result set given these limitations by using a WHERE/HAVING clause. I have had no luck.

Thanks.

 

[esdee]

doesnt it work with "select top N " ?

 

[mkal]

That would only bring me back 5 rows. I need to bring back 5 rows for each visitor in a given project. Like this:

ID Project PageID NumberOfTimesViewed
1 22 Home 25
1 22 Products 20
1 22 about us 15
1 22 tips 14
1 22 maps 12
2 33 Home 35
2 33 maps 24
2 33 Products 19
2 33 maps 14
2 33 tips 10
11 56 Home 29
11 56 Products 17
11 56 about us 15
11 56 tips 4
11 56 maps 2
.
..
... more of the same.

Some will have five top pages others will have only 2-3 or what ever but I want to limit it to just the top five.

Thanks,

 

[rasETL]

mkal

First, not sure why this in the the DTS forum...

However, one alternative is to store the result set in a temporary (on the fly) table, then execute a second query selecting the top five pageIDs.

Hope that helps.