Why am I getting additional rows?? 2

[Goalie3533]

I have a query that should diplay the 3 top selling items from a particular product category(i.e: Clothes, Hats, Shoes, etc). I'm joining 2 tables to gather all of the necessary information however more than 3 rows are being returned for some strange reason. I'm selecting the product ID #(catalogid), total quantity of the product sold(sum(oitems.numitems)) and joining those values with my Product Categories table(prodcategories) so I can match it up with one particular category(347). Any idea what's wrong with my query that would enable it to return more than 3 rows?
----------------------------------------------
select TOP 3 oitems.catalogid, sum(oitems.numitems) as sumofnumitems from oitems inner join prodcategories on oitems.catalogid=prodcategories.intcatalogid where prodcategories.intcategoryid=347 GROUP BY oitems.catalogid ORDER BY sum(oitems.numitems) DESC
------------------------------------------------

Thanks in advance.

-Goalie35

 

[JonFer]

If 2 items have the same sum(oitems.numitems) value and are in the top 3 ranking, both will be displayed so you'll have 4 records in the result.



John

http://www.interautomateconsulting.com

 

[SonOfEmidec1100]

Nothing wrong with the query, possibly you have equal values.

The TOP predicate does not choose between equal values, all rows with the TOP 3 values will be returned.

 

[SonOfEmidec1100]

To be technically correct - The TOP predicate does not choose between equal values [blue]in the ORDER BY clause[/blue].

So as an afterthought because the catlogid is unique you should be able to correct the problem by adding the catalogid field to the ORDER BY.

I think...

 

[SQLScholar]

I had a similar thing with similar code.

It doesnt seem to like a top with a group by. Do a second query to query this one, then put the top in that.

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!