CODEWITHSUNDEEP
sqlms-access
iDevlop
iDevlop

Reputation: 25272

Access query: find highest rank product in each category

I have a query that calculates product rank per Category: 

qryEvalProd    
category Product   Rank
-------- -------   ----
Cat1     Prod6     1254
Cat1     Prod1      950
Cat1     Prod2      800
Cat2     Prod3     1500
Cat2     Prod5      950

I want to make a query on that, to return the best product for each category:

category Product   
-------- -------   
Cat1     Prod6     
Cat2     Prod3

I know I could do that using a correlated subquery containing a group by and Max, however but for performance reason I am trying to make it in one shot using Group By and First. But I can't express the fact I want the First Prod of each cat sorted by DESC Rank.

Is there a way to do that in one pass ?

Upvotes: 0

Views: 2727

Answers (4)

Shawn
Shawn

Reputation: 9442

This tweak lets you add the ORDER you wanted. It uses a subquery...but not a correlated subquery, so performance is not impaired:

SELECT Category, First(Product) AS BestProduct
FROM (
  SELECT Category, Product, Rank
  FROM qryEvalProd
  ORDER BY Category, Rank DESC
) AS Ordered
GROUP BY Category;

Upvotes: 1

Brian Camire
Brian Camire

Reputation: 4825

You might start with something like this:

SELECT Category, Product
FROM qryEvalProd
WHERE Product = 
    (SELECT TOP 1 Self.Product 
    FROM qryEvalProd AS Self
    WHERE Self.Category = qryEvalProd.Category
    ORDER BY Self.Rank DESC)

If there is the possibility of ties (that is, two products in the same category with the same rank), this query will arbitrarily pick one of them.

If you want it to always pick the same one, you can probably make it do what you want by changing the the ORDER BY clause. For example, if in the event of a tie, you want it to pick the one with the first Product value in alphabetical order, you might change the ORDER BY clause to:

ORDER BY Self.Rank DESC, Self.Product

If you want it instead to list all the products with the highest rank, you might use a query like this:

SELECT Category, Product
FROM qryEvalProd
WHERE Rank = 
    (SELECT Max(Self.Rank) 
    FROM qryEvalProd AS Self
    WHERE Self.Category = qryEvalProd.Category)

Upvotes: 0

iDevlop
iDevlop

Reputation: 25272

In fact this seems to work. I am just a bit annoyed because I can't force in my statement the order of the input dataset, but anyway, it wors.. 

SELECT Category, First(Product) AS BestProduct
FROM qryEvalProd    
GROUP BY Category;

I can add ORDER BY Category without any impact on the output. I would have liked to add ORDER BY Category, Rank to be sure, but that's not accepted.

If someone has a better suggestion you're still welcome.

Upvotes: 0

Ryan
Ryan

Reputation: 6866

Perhaps the use of the HAVING clause may help?

Upvotes: 0

Related Questions