TOP 10 by categories - 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

Transact-SQL (2000)

TOP 10 by categories

Author

Topic

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-14 : 15:09:29

I need to retrieve top 10 products in each category. So far, I've come up with the following query:

SELECT t1.product_id, t1.category, t1.total_sold, rank=COUNT(*)
FROM (SELECT product_id, category, SUM(quantity) AS 'total_sold'
FROM #for_tempTop
GROUP BY category, product_id) t1
INNER JOIN
(SELECT product_id, category, SUM(quantity) AS 'total_sold'
FROM #for_tempTop
GROUP BY category, product_id) t2
ON t1.category=t2.category
WHERE t1.total_sold<=t2.total_sold
GROUP BY t1.category, t1.product_id, t1.total_sold
HAVING COUNT(*)<=10
ORDER BY t1.category, rank

This query has three problems:
1. If I have five products with the same total_sold and eight that sold in higher quantities, then rank is apparently 12, and this query will not retrieve it so far. So, I'll will end up with 8 instead of 10.
2. Also, if the rank is the same, then I'll see the same #, and I need to get a sequence.
3. I only want to retrieve categories, which have top 10 or more. Those ones, which sold only say 5 products, I would like to drop.

(I've being doing this using a cursor, which I would like to replace).

helena

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-14 : 16:25:49

How about:

SELECT TOP 10 WITH TIES product_id, category, SUM(quantity) total_sold
FROM #for_tempTop
GROUP BY category, product_id
ORDER BY SUM(quantity) DESC

I'm not sure if that gets what you want, but maybe you can tweak it a little.

-Chad

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-14 : 16:44:49

quote:

This query has three problems:
1. If I have five products with the same total_sold and eight that sold in higher quantities, then rank is apparently 12, and this query will not retrieve it so far. So, I'll will end up with 8 instead of 10.
2. Also, if the rank is the same, then I'll see the same #, and I need to get a sequence.

What is the tie breaker? Do you want exactly ten? Or would you want all 12?

quote:

3. I only want to retrieve categories, which have top 10 or more. Those ones, which sold only say 5 products, I would like to drop.

You can simply add this to your outer where clause.

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-18 : 14:48:38

Shame on both of you. Chad, your query will return top 10 - period, ties or no ties.

Why do you thing I wrote mine that complicated, if it is that simple?

I really don't thing any one should post just to get a higher score here without knowing the right answer.

So far, I'm disappointed. I know that this forum doesn't have OLAP gurus, but this one...?

helena

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-18 : 15:14:53

Well Helena, if you're going to chastise the people trying to help you (which I have never found to be an effective approach) then the least you should do is provide some DDL and DML statements that will generate test data for people to work with. Also include your desired resultset based on the supplied test data.

------------------------
GENERAL-ly speaking...

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-18 : 15:14:56

quote:

Shame on both of you. Chad, your query will return top 10 - period, ties or no ties.

Why do you thing I wrote mine that complicated, if it is that simple?

I really don't thing any one should post just to get a higher score here without knowing the right answer.

So far, I'm disappointed. I know that this forum doesn't have OLAP gurus, but this one...?

helena

Helena,
I post here to help people, not as "you assume to get a higher score". My post was simply an effort to clarify your requirement. I assume the rest of the gurus on this site are also unable to answer your post because we have no rules to resolve your problems. If you would like help, answer my questions. I will rephrase them in hopes that maybe you will be better able to understand what it is I am lacking.

quote:

1. If I have five products with the same total_sold and eight that sold in higher quantities, then rank is apparently 12, and this query will not retrieve it so far. So, I'll will end up with 8 instead of 10.

This is what you are getting now. What do you want instead? Do you want to return all twelve? If not, what of the tied bunch makes one better than the others or do you not care?

quote:

2. Also, if the rank is the same, then I'll see the same #, and I
need to get a sequence.

What, if anything, distinguishes one records with the same total_sold in terms of order?

Finally, Yeah Todd(that's me) for not swearing or being rude in any other significant way in this post. It is hard to be publicly shamed. That deserves a

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-18 : 15:30:41

Helena,

Look, I post on this board to make sure people get help with SQL Server, so it will become the most widely used DBMS, I really could care less about the "Score".

I posted what I did because I really couldn't make heads or tails of what you were "Trying" to get. I was hoping to get more information from you as to where my "Stab in the dark" fell short.

How am I supposed to know what level you are at? Believe me, I have seen some people make extremely complicated what is a simple query.

You work on explaining what you want, and then maybe someone can give you an answer (And for heavens sake, if you do try to answer MAKE SURE YOU GET IT RIGHT THE FIRST TIME, OR SUFFER THE WRATH OF HELENA!)

quote:

So far, I'm disappointed. I know that this forum doesn't have OLAP gurus, but this one...?

If "This one" is so easy, why did you post it?

-Chad

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-18 : 16:43:11

Well, I guess your pride is hurt, but you still have no aswer.

My question is classic: how can I TOP work on GROUPs. Basically, how to get around the fact that if a query is written the way you did, the only answer it returns is simple TOP.

To make it easier on you, I'll run my query and yours and post both results, may be then you'll understand what the problem is.

And just to be honest with you, I was hoping to nr or robvolk or izaltman read it and may be come up with the aswer.

Product_id Category Total_sold rank
C646 Category 1 20 1
C651 Category 1 15 2
C477 Category 1 14 3
C279 Category 1 13 4
C925 Category 1 12 5
C3849 Category 1 9 7
C2021 Category 1 9 7
C2022 Category 1 8 10
C0069 Category 1 8 10
C2132 Category 1 8 10
STDV0482 Category 2 61 1
STDV1655 Category 2 47 2
STDV1656 Category 2 40 3
BUDV0105 Category 2 32 4
BUDV0048 Category 2 26 5
STDV1724 Category 2 24 6
STDV1380 Category 2 23 9
STDV2484 Category 2 23 9
STDV1472 Category 2 23 9
STDV2174 Category 2 21 10
STDV2155 Category 3 35 1
STDV0978 Category 3 33 2
STDV0172 Category 3 26 3
STDV1365 Category 3 19 4
STDV0564 Category 3 16 6
STDV2483 Category 3 16 6
STDV0848 Category 3 15 8
STDV1436 Category 3 15 8

Above is what I get

C646 Category 1 20 1
C651 Category 1 15 2
C477 Category 1 14 3
C279 Category 1 13 4
C925 Category 1 12 5
C3849 Category 1 9 6
C2021 Category 1 9 7
C2022 Category 1 8 8
C0069 Category 1 8 9
C2132 Category 1 8 10
STDV0482 Category 2 61 1
STDV1655 Category 2 47 2
STDV1656 Category 2 40 3
BUDV0105 Category 2 32 4
BUDV0048 Category 2 26 5
STDV1724 Category 2 24 6
STDV1380 Category 2 23 7
STDV2484 Category 2 23 8
STDV1472 Category 2 23 9
STDV2174 Category 2 21 10

This is what I need to get (rank as a sequence, category 3 – out, since it only has less then 10, etc.). Shortly, I only need categories, which sold more than 10 titles.

And finally, this is what you get from your query
C0047 Category 10 182
STDV2028 Category 9 152
STDV0870 Category 10 150
STDV1117 Category 10 119
STDV1091 Category 10 116
STDV0581 Category 9 112
STDV1009 Category 10 110
STDV0539 Category 9 102
STDV0223 Category 9 93
STDV1375 Category 4 79

Notice that here are all the results you got (I only posted a small portion of mines, since I do get top_sold from all categories). You also missed the rank.

And one more thing here. An error you made is common for a novice, which I guess you’re not. That’s why I suggested you read before you post and be more responsible.

And for the closure: I posted it, because it's hard - that's the only reason.

helena

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-18 : 17:01:17

You never did answer Todd's original question. What is the tiebreaker?

You have as desired output:
...
C2021 Category 1 9 7
C2022 Category 1 8 8
C0069 Category 1 8 9
C2132 Category 1 8 10

But what makes C2022 ranked higher than C0069? They both sold 8 units? There is no basis for ranking it higher.

The error I made was due to lack of information. I will refrain from TRYING to help on your posts that do not clearly state their objectives, and leave them unanswered. You are very lucky that anyone is attempting to help you given your attitude.

-Chad

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-18 : 18:29:54

Contrary to your belief, I am fully qualified to answer your post and
due to my innate ability to ignore your predilection toward rudeness, I am
willing to do so.

Create Table #TEMP (
Product_id Varchar(10),
Category VARCHAR(15),
Total_sold INT)

Insert #TEMP VALUES('C646', 'Category 1', 20 )
Insert #TEMP VALUES('C651', 'Category 1', 15 )
Insert #TEMP VALUES('C477', 'Category 1', 14 )
Insert #TEMP VALUES('C279', 'Category 1', 13 )
Insert #TEMP VALUES('C925', 'Category 1', 12 )
Insert #TEMP VALUES('C3849', 'Category 1', 9 )
Insert #TEMP VALUES('C2021', 'Category 1', 9 )
Insert #TEMP VALUES('C2022', 'Category 1', 8 )
Insert #TEMP VALUES('C0069', 'Category 1', 8 )
Insert #TEMP VALUES('C2132', 'Category 1', 8 )
Insert #TEMP VALUES('STDV0482', 'Category 2', 61)
Insert #TEMP VALUES('STDV1655', 'Category 2', 47 )
Insert #TEMP VALUES('STDV1656', 'Category 2', 40 )
Insert #TEMP VALUES('BUDV0105', 'Category 2', 32 )
Insert #TEMP VALUES('BUDV0048', 'Category 2', 26 )
Insert #TEMP VALUES('STDV1724', 'Category 2', 24 )
Insert #TEMP VALUES('STDV1380', 'Category 2', 23 )
Insert #TEMP VALUES('STDV2484', 'Category 2', 23 )
Insert #TEMP VALUES('STDV1472', 'Category 2', 23 )
Insert #TEMP VALUES('STDV2174', 'Category 2', 21 )
Insert #TEMP VALUES('STDV2155', 'Category 3', 1)
Insert #TEMP VALUES('STDV0978', 'Category 3', 33)
Insert #TEMP VALUES('STDV0172', 'Category 3', 26)
Insert #TEMP VALUES('STDV1365', 'Category 3', 19)
Insert #TEMP VALUES('STDV0564', 'Category 3', 16)
Insert #TEMP VALUES('STDV2483', 'Category 3', 16)
Insert #TEMP VALUES('STDV0848', 'Category 3', 15)
Insert #TEMP VALUES('STDV1436', 'Category 3', 15)

Since you will not tell me what you want to be the tiebreaker and it
is not apparent from the result set your posted I can not produce the
result set you request. However, In this query I have assumed that
ProductID is a reasonable tiebreaker for demonstration purposes. You
could use anything though.


SELECT t1.product_id, t1.category, t1.total_sold, rank=COUNT(*)
FROM #TEMP t1
INNER JOIN 
	#Temp t2
		ON t1.category=t2.category 
WHERE (t1.total_sold<t2.total_sold OR (t1.total_sold =t2.total_sold AND t1.product_id<=t2.product_id))
	AND t1.total_sold > 10
GROUP BY t1.category, t1.product_id, t1.total_sold
HAVING COUNT(*)<=10
ORDER BY t1.category, rank

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-18 : 19:33:03

Chad,
#1 - I don't have an attitude and I appreciate you stop scolding me here. I don't solicit help. You're welcome to review my posts - most of them are answers, not questions. I have hardly posted six questions, got answers which were REALLY helpful about three times, and the other three just confirmed my own results.

#2 - there is no scientific tie breaker - I simply need TOP 10. The rank should not be a true rank - just a sequence from 1 to 10. In theory - if there are five products sold in the same quantities - they all get the same rank, in reality I need to display them one after another - that's all.

helena

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-18 : 20:14:20

quote:

#2 - there is no scientific tie breaker - I simply need TOP 10. The rank should not be a true rank - just a sequence from 1 to 10. In theory - if there are five products sold in the same quantities - they all get the same rank, in reality I need to display them one after another - that's all.

helena

Helena,

I think we are getting close.

Let me rephrase the "tie breaker" question a bit. When there are ties what should happen? If include then with ever increasing rank, try this:

-- Added these folks to this test cases above to prove that this works
Insert #TEMP VALUES('STDV2176', 'Category 2', 21 )
Insert #TEMP VALUES('STDV2177', 'Category 2', 21 )
Insert #TEMP VALUES('STDV2178', 'Category 2', 21 )


SELECT t1.product_id, t1.category, t1.total_sold, rank=COUNT(*)
FROM #TEMP t1
	INNER JOIN #Temp t2
		ON t1.category=t2.category 
WHERE (t1.total_sold<t2.total_sold OR 
	(t1.total_sold=t2.total_sold AND t1.product_id<=t2.product_id))	AND
		t1.Total_Sold >= (SELECT TOP 1 total_sold
			FROM(SELECT Top 10 total_sold
				FROM #Temp 
				WHERE Category = T1.Category AND total_sold > 10
				ORDER BY Total_Sold Desc) AS A
			ORDER BY total_sold ASC) 
GROUP BY t1.category, t1.product_id, t1.total_sold
ORDER BY t1.category, rank

If you want to stop at ten, see the code from my previous post.

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-18 : 20:33:20

Todd,
here is my original query re-written with your clause

SELECT t1.product_id, t1.category, t1.total_sold, rank=COUNT(*)
FROM (SELECT product_id, category, SUM(quantity) AS 'total_sold'
FROM #for_tempTop
GROUP BY category, product_id) t1
INNER JOIN
(SELECT product_id, category, SUM(quantity) AS 'total_sold'
FROM #for_tempTop
GROUP BY category, product_id) t2
ON t1.category=t2.category
WHERE (t1.total_sold<t2.total_sold OR (t1.product_id<=t2.product_id AND t1.total_sold=t2.total_sold))
GROUP BY t1.category, t1.product_id, t1.total_sold
HAVING COUNT(*)<=10
ORDER BY t1.category, rank

AND I THINK IT WORKS
I apologize to you for not having to read your post with the attention it deserved. I was after Chad's post and his query sort of ticked me off, since it demonstrated lack of basic idea how TOP works.

THANKS YOU VERY MUCH

PS Sorry, you had to write another query - you got it right the first time (I just removed total_sold < 10 - it doesn't matter)

helena

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-18 : 20:42:58

Chad,
just one more word to you. Sometimes you just have to accept defeat and plainly admit that you screwed up - without trying to explain why you wrote a query with SELECT TOP AND GROUP BY hoping it will SELECT TOPs FROM all groups. I wish it was that simple.

Nothing personal here - I checked your other posts and I think you pretty good at what you do.

I hope that's it.

helena

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-19 : 12:06:26

Helena,

I will never admit defeat (I didn't even realize we were in some sort of battle?). I did not get the query right because you didn't explain what you wanted. Maybe you should admit that. After seeing my post, a simple, "That's not quite it, let me rephrase what I am looking for..." or something along those lines would have been much more appropriate.

This is supposed to be about learning, do you think new SQL users will attempt to answer questions if they fear public ridicule if they get the wrong answer?

I have a perfect understanding of how TOP works, again your original post lacked the detail it required (It may have seemed sufficiet to you, but you work with the data, you understand the requirements of the query).

There was no need for anyone to get bent out of shape on this thread. The ONLY reason it happened is because you chastised Todd and I for ATTEMPTING to help you.

You could have very easily restated your requirements, and gotten your answer, but you chose to berate those who attempted to help. I consider that a bad attitude.

In the future, I hope that you can accept incorrect answers with better restraint.

I know I am good at what I do, I don't need you to tell me, I have helped hundreds of people on this forum. I guess 250-1 ain't a bad record.

This will be my last post on this thread, as I have better use of my time.

-Chad

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-19 : 12:26:30

Why can't we all just get along?

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-19 : 12:39:56

That's exactly my point.

I will repeat here. An attempt to write SELECT WITH TOP and GROUP BY is plain wrong and has nothing to do with anything else.

Probably, I should've ingnored it, read Tod's question and moved on.

A reply like "I don't need to you tell me that I'm good" is not even arogant - that's just rude, same as the rest of scolding.

Chad, I gladly invite you to ignore all my posts - questions or answers - may be that will give you some satisfaction.

PS - number of posts doesn't mean anything - thanks to you I got extra five or six just simply trying to close this subject and by the way my # is close to yours.

helena

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-02-19 : 12:49:04

If you say

quote:
That's exactly my point.

then why are you continuing to argue over this. Im not saying anyone is in the wrong here but sometimes it is best to not say anything at all in this matter. And from reading this thread all of this would have been avoiding if you would have done just that. In my own opinion Chadmat was just trying to help. There is no guarantee that anyones answers will work correctly and if his didn't work thats all you had to say, instead of stating

quote:
I really don't thing any one should post just to get a higher score here without knowing the right answer.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-19 : 17:01:03

Come on guys thats enough. Be nice.

Chad, realise that Helena has been around here a long time and is usually nothing but helpful.

Helena, realise that Chad in a short time here has helped a huge number of people. Also remember that he doesn't know you and your knowledge level. Then think about the number of posts we get here where requirements are ill defined and people are doing something complex when they didn't need to be.

Let's just let this thread go and get on with our lives.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources