Could you please suggest a simple way to calculate rank on multiple columns in Oracle 19c.
For instance - considering [ Mango | 1 | 1 ] group from the table
NAME DAY PROD S M P AMT1 AMT2 AMT3
----------------------------------------------------------
MANGO 1 1 -2.75 3 15 21.277 80 80
MANGO 1 1 -2.75 1.5 21 27.778 80 80
MANGO 1 1 -2.75 3 21 27.778 80 80
MANGO 1 1 -2.75 3 14 20 80 80
Expected output : 2nd row
For each group [NAME, DAY, PROD], I need to identify a row which has min[abs(S)] value. If this returns multiple rows with same values [NAME, DAY, PROD, S], then next column to be considered is row having max(AMT1). Again in case of more than one row is fetched with same [NAME, DAY, PROD, S, AMT1], next factor to be considered is min(M). Lastly, min(P).
Overall, a single row should be returned for each group [NAME, DAY, PROD] considering order for ranking rows as min(abs(S)) -> max(AMT1) -> min(M) -> min(P)
Expected Output for sample Records : Provided SQLs in DB_Fiddle
NAME DAY PROD S M P AMT1 AMT2 AMT3 final deciding factor
----------------------------------------------------------
APPLE 1 1 -2.5 3 21 27.778 80 80 ---> min(abs(S))
APPLE 8 0.5 -1.25 3 10 51.02 90 90 ---> max(AMT1)
MANGO 5 1 -1.75 3 14 24 83.333 83.333 ---> min(P)
MANGO 1 1 -2.75 1.5 21 27.778 80 80 ---> min(M)
Just use the ROW_NUMBER() analytic function:
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER (
PARTITION BY name, day, prod
ORDER BY ABS(s) ASC,
amt1 DESC,
m ASC,
p ASC
) AS rn
FROM test3 t
)
WHERE rn = 1;
Which, for the sample data, outputs:
NAME DAY PROD S M P AMT1 AMT2 AMT3 RN APPLE 1 1 -2.5 3 21 27.778 80 80 1 APPLE 8 .5 -1.25 3 10 51.02 90 90 1 MANGO 1 1 -2.75 1.5 21 27.778 80 801 MANGO 5 1 -1.75 3 14 24 83.333 83.333 1
db<>fiddle here