a ranking question

[tanya]

Hello,

I have a ranking problem.
Currently my query ranks each item (According to price)
within a particular category. If the prices are the
same, the ranking function switches to the itemID and
looks for the lower ID.

SELECT Q.Category, Q.ItemID, Q.Price
(SELECT COUNT(*) + 1 FROM Pricelist Q1
WHERE Q1.Price > Q.Price OR (Q1.Price = Q.Price
AND Q1.ItemID < Q.ItemID))
AS Rank,
FROM Pricelist AS Q;

I need to be able to run this query and have it rank each
category separately, rather than ignoring the category
and ranking every record in the query.

I would appreciate any help on this one.

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT Q.Category, Q.ItemID, Q.Price
(SELECT COUNT(*) FROM Pricelist Q1
WHERE
Q1.Category = Q.Category
AND
(Q1.Price >= Q.Price OR
(Q1.Price = Q.Price
AND Q1.ItemID <= Q.ItemID)))
AS Rank,
FROM Pricelist AS Q;

 

[Duane Hookom]

SELECT Q.Category, Q.ItemID, Q.Price
(SELECT COUNT(*) + 1 FROM Pricelist Q1
WHERE Q1.Category = Q.Category AND Q1.Price > Q.Price
OR (Q1.Category = Q.Category AND Q1.Price = Q.Price
AND Q1.ItemID < Q.ItemID))
AS Rank,
FROM Pricelist AS Q;