Select Top X by Items by Category

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)

Select Top X by Items by Category

Author

Topic

codewzrd
Starting Member

8 Posts

Posted - 2005-05-02 : 12:48:52

I have a table that contains the Category, the Items in that category and the Count of Items bought. I would like to get the Top 10 items per category.

The table structure is...
ID idxCategory Item Total
1 1 Bike 12
2 1 Scateboard 5
3 2 Flowers 18
4 2 Shrubs 543
5 2 Trees 24

The query I have is not quite correct...
SELECT idxCategory, Item, Total
FROM tCategory e WITH (NOLOCK)
WHERE Item IN
(
SELECT TOP 10 Item
FROM tCategory WITH (NOLOCK)
WHERE idxCategory = e.idxCategory
ORDER BY Total DESC
)
ORDER BY idxCategory, Total DESC

Thanks for any help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-02 : 13:56:24

I don't see a problem. Why is it "not quite correct"?

Be One with the Optimizer
TG

codewzrd
Starting Member

8 Posts

Posted - 2005-05-02 : 15:13:02

Never mind. It turns out that I didn't do a sort by Item in the inner query. So the last few rows were different.

Thanks for making me take a closer look at the query.

The answer is...

SELECT idxCategory, Item, Total
FROM tCategory e WITH (NOLOCK)
WHERE Item IN
(
SELECT TOP 10 Item
FROM tCategory WITH (NOLOCK)
WHERE idxCategory = e.idxCategory
ORDER BY Total DESC, Item
)
ORDER BY idxCategory, Total DESC, Item

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-02 : 15:39:11

This should give the top 10 per category, try it;

SELECT
	t1.idxCategory
	,t1.Item
	,t1.Total
FROM
	tCategory t1
WHERE
	11 > (	SELECT COUNT(*) FROM tCategory t2
		WHERE t1.idxCategory = t2.idxCategory
		AND t1.Total <= t2.Total )

rockmoose

Ok, saw that You just fixed it...
Item must be Unique in tCategory for the IN clause to work properly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources