Top x by group query? - 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)

Top x by group query?

Author

Topic

codewzrd
Starting Member

8 Posts

Posted - 2005-03-28 : 13:09:00

I've got a table that represents how many times a domain was visited by each user...

[code]
UserID DateCreated Domain Total
1 032005 yahoo.com 2
1 032005 msn.com 4
1 032005 google.com 12
...
2 032005 cnn.com 3
2 032005 msn.com 31
2 032005 google.com 1
...
3 032005 cnet.com 5
...

What I'd like to do is show the top 3 domains visited by each user.

I tried to write a query like so...

SELECT TOP 3 UserID, Domain, DateCreated, Total
FROM tDomainCount WITH (NOLOCK)
WHERE EXISTS
(
SELECT *
FROM tDomainCount WITH (NOLOCK)
GROUP BY UserID, Domain
)
ORDER BY UserID, Total DESC
GO

Is it possible to write a query without using cursors?

Thanks for your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-28 : 14:25:24

Yes, it's possible to do without cursors. Since you included a column that isn't in your tDomainCount table,(DateCreated), this ~~will~~ should just give you what your question asked for (top 3 domains visited by each user):


SElect	UserID, Domain
from	tDomainCount e with (nolock)
Where	Domain IN (
		Select top 3 Domain 
		From tDomainCount with (nolock) 
		where UserID = e.UserID 
		order by total desc
		)
group by UserID, Domain
Order by 1,2

Be One with the Optimizer
TG

codewzrd
Starting Member

8 Posts

Posted - 2005-03-28 : 14:58:39

Oops. DateVisited and DateCreated are one and the same. I just typed DateVisited in by accident. Sorry about that. I tried your sample and it's very close to what I want. It is returning the 5 rows for each user. But the counts are not correct. I keep getting the lowest counts. I'll keep playing with your sample.

My results are...


User	Domain		DateCreated	Total
1	cnn.com		200503		169
1	google		200503		4
1	freecenter.com	200503		3
1	msn.com		200503		2
1	yahoo.com	200503		1

Results should be ...


User	Domain			DateCreated	Total
1	foxnews.com		200503		1316
1	match.com		200503		495
1	jdate.com		200503		392
1	courttv.com		200503		331
1	finance.yahoo.com	200503		288

Thanks for the help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-28 : 15:33:22

Its probably they way you're retrieving the DateCreated and Total values. Try this:


select	a.UserID
	,a.Domain
	,max(DateCreated) DateCreated
	,a.Total
from	tDomainCount a with (nolock)
JOIN	(
	SElect	UserID
		,Domain
		,max(total) Total
	from	tDomainCount e with (nolock)
	Where	Domain IN (
			Select top 3 Domain 
			From tDomainCount with (nolock) 
			where UserID = e.UserID 
			order by total desc
			)
	group by UserID, Domain
	) b
		ON a.UserID = b.UserID
		and a.Domain = b.Domain
		and a.Total = b.Total
Group by
	a.UserID
	,a.Domain
	,a.Total

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources