Using RANK() to assign a rank based on multiple criteria in Oracle-CodePudding

I have a table similar to:

Item	Class	Qty	Loc
Apple	Fruit1	1	N
Apple	Fruit1	1	NW
Apple	Fruit2	0	W
Apple	Fruit3	1	N
Orange	Fruit1	10	SE
Orange	Fruit2	1	SW

I am trying to query all rows with the lowest Class that has the smallest non-zero Qty for each Item. So if the same Item has the same Qty for multiple classes, it would select the lowest Class (Fruit1 < Fruit2 < Fruit3 < ...). I've tried using something like:

SELECT A.*, 
RANK() OVER(
PARTITION BY ITEM, CLASS
ORDER BY QTY ASC, CLASS ASC) AS item_rank
FROM fruits.info
WHERE QTY <> 0

to get a ranking to select, which isn't working. The results of the ranking should be:

Item	Class	Qty	Loc	item_rank
Apple	Fruit1	1	N	1
Apple	Fruit1	1	NW	1
Apple	Fruit3	1	N	2
Orange	Fruit1	10	SE	2
Orange	Fruit2	1	SW	1

I've would then use a nested select for item_rank = 1:

SELECT B.* FROM (
    SELECT A.*, 
    RANK() OVER(
    PARTITION BY ITEM, CLASS
    ORDER BY QTY ASC, CLASS ASC) AS item_rank
    FROM fruits.info
) B
WHERE B.item_rank = 1

to get the final results, which should be:

Item	Class	Qty	Loc	item_rank
Apple	Fruit1	1	N	1
Apple	Fruit1	1	NW	1
Orange	Fruit2	1	SW	1

How can I structure my RANK() in a way to achieve this? Is there a more efficient way?

CodePudding user response：

class should only be used in ordering, not in partitioning. You want ranks for each item, not for each pair of item and class.

The (easy to fix) mistake in your attempt was to include class both in partition by and in order by; if you think about it for a second, that NEVER makes sense: if you partition by something, then further ordering by it (in each partition, where "it" is constant) makes no sense. Remove class from partition by and you should get what you need.