Top 10 from each subcategory? - 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 10 from each subcategory?

Author

Topic

derketo
Starting Member

28 Posts

Posted - 2005-05-14 : 19:23:37

I'm trying to pull the top 10 from each subcategory into one query. So bascially I need to do a distinct search on the subcategory and then loop through each distinct result set to pull in the top 10. Anyone know how to do this? Thanks ahead of time.

-D

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-14 : 20:23:46

please define what you mean by "top 10" .... by what criteria/sort ?

- Jeff

derketo
Starting Member

28 Posts

Posted - 2005-05-14 : 20:25:23

select top 10 email from foo
where subcategory = 'abc'
order by zipcode

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-14 : 21:14:32

Since you didn't post any DDL or sample data I didn't test this. Does it work for you?


select	subcategory
	,email
from	foo a
where	email IN (select top 10 email 
		from foo 
		where subcategory = a.subcategory 
		order by zipcode)

Be One with the Optimizer
TG

derketo
Starting Member

28 Posts

Posted - 2005-05-14 : 22:28:34

This is close. The only thing is that the subcategory needs to be distinct.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-15 : 10:13:16

Post an example of your desired results (and the DDL of the table). I don't see how subcategory can be distinct if you want 10 email records for each subcategory.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources