SQL compare one column, then another, by using max over partition by-CodePudding

DB: SAP HANA

I have asked this question before, but now I'm facing more complicated question. When qty is the same, I want to return biggest no.

user	no	qty
A	10	20
A	11	20
B	12	40
B	13	10

id	user
1	A
2	B

Expected result

id	user	no
1	A	11
2	B	12

I try

SELECT
    B.id,
    B.user,
    C.max_qty_no
FROM
    B
LEFT JOIN (
    SELECT 
        A.user,
        CASE
            WHEN A.qty = (
              MAX(A.qty) OVER (PARTITION BY A.user)
            ) THEN A.no
        END as max_qty_no
    FROM
        A
) C ON C.user = B.user AND
       C.max_qty_no IS NOT NULL;

return

id	user	no
1	A	10
1	A	11
2	B	12

CodePudding user response：

You want to rank the A rows per user and only select the best-ranked row. So far this ranking was on one column only, so you could simply compare the value with the maximum value. Now, however, the ranking must be done considering two columns instead of just one. You can use ROW_NUMBER for this ranking:

select id, user, no
from
(
  select
    b.id, b.user, a.no,
    row_number() over (partition by b.user order by a.qty desc, a.no desc) as rn
  from a
  join b on b.user = a.user
) ranked
where rn = 1;

CodePudding user response：

Since you want the MAX(no) per user having the largest quantity you need to apply additional selection criteria. The partitioning takes care of selecting the rows with MAX(qty) per user but you still need to select the rows with MAX(no) for each distinct user - you can do this by using the MAX aggregate function combined with a GROUP BY. With this small change you can return the expected results:

SELECT
    B.id,
    B.user,
    MAX(C.max_qty_no)
FROM
    B
LEFT JOIN (
    SELECT 
        A.user,
        CASE
            WHEN A.qty = (
              MAX(A.qty) OVER (PARTITION BY A.user)
            ) THEN A.no
        END as max_qty_no
    FROM
        A
) C ON C.user = B.user AND
       C.max_qty_no IS NOT NULL
GROUP BY B.id, B.user;