Find how an item ranks in a table?

Is it possible to find the position of an item without retrieving all the records in the table?

I have a products table, and want to find out how its sales are doing within a particular category.

My table consists of the following columns of interest:

ProductID, CategoryID, ItemsSold.

So, how would I turn the following query into an (not sure if the terminology is correct) aggregate query?

SELECT *FROM dbo.ProductsWHERE CategoryID = 10ORDER BY ItemsSold

Also, is it possible to include the SUM() of the items (in the same category) in the aggregate function or would I need to perform a separate query?

Any help would be very much appreciated.

Thanks.

The only way I can see of doing this is by creating a store procedure that returns the required values. Note that the only part I am not sure about is if the insert will work with an ORDER BY.

EDIT : If you are using SQL Server 2005, then have a look into therow_number()function

e.g.

CREATE STOREDPROCEDURE getProductRank
(
@.categoryIDint,
@.productIDint
)AS
CREATE TABLE #t
(
IDint IDENTITY(1,1),
ProductIDint,
CategoryID,
ItemsSold
)
INSERT INTO #t
(SELECT *FROM dbo.Products
WHERE CatehoryID = @.categoryID
ORDER BY ItemsSold)
DECLARE @.rankint
SELECT @.rank = IDFROM #t
WHERE ProductID = @.productID
RETURN @.rank
GO

|||

Hi Jagdipa,

I am using SQL Express 2005 and your reference was very helpful, however it still didn't remove the problem that I didn't want to retrieve all the records in the Category, as I only require 1 of the Products from the table (apologies for being unclear on this in the first post, I think you noticed as you declared a parameter - ProductID = @.ProductID).

I would like to call an aggregate function, something similar to the SUM() function as I mentioned previously.

I'm a little new to SQL so only the basic statements, and don't even know if its possible to achieve what I'm looking for without creating a temporary view or table as you've done?

Thanks.

|||

For those looking for the answer to this question, I have modified this from a custom paging example (which there appears to be lots of the web) to find the rank of an item in a table.

SELECT * FROM

(SELECT ROW_NUMBER() OVER (ORDER BY ItemsSold DESC) AS ProductRankNo, * FROM Products WHERE CategoryID = 10) AS AllProducts

WHERE ProductID = 23

To find the TOP 10 TOPSELLERS, simply change the "WHERE ProductID = 23" to "WHERE ProductRankNo BETWEEN 1 AND 10".

Bear in mind this will only work on SQL Server 2005 databases.

Initially, I was worried that the inner SELECT call would retrieve ALL the records in the table, but this doesn't appear to be so.