Grouping and ranking issue: based on table shown, script or stored procedure should return ranking based on provided rules

Question

Using this table, a query or stored procedure should return rank for each row as shown in rank table content. Rules are provided below.

hearderid	iscacId	paymentAmt
1234	1162	1000
1234	1161	200
1234	1163	300
1221	1160	900
1221	1165	100
1220	1159	3000
1223	1178	4000
1250	1130	800

Using this table, I have to create the rank table based on following rules:

1. Rank calculated based on highest amount desc

2. Different Header ID - Different ISCACID = Consider it as Individual Record

3. Same Header ID - Different ISCACID = Consider it as Individual group of Records

4. Unique Header ID – Unique ISCACID = Consider it as Individual Record

5. if headerid is same but iscacId is different first give rank to this group based on amount

hearderid	iscacId	paymentAmt	Rank
1234	1162	1000	3
1234	1161	200	5
1234	1163	300	4
1221	1160	900	6
1221	1165	100	7
1220	1159	3000	2
1223	1178	4000	1
1250	1130	800	8

I tried this using below query but unable to achieve it.

WITH Ranked AS 
(
    SELECT
        headerId,
        iscacId ,
        paymentAmt ,
        ROW_NUMBER() OVER(PARTITION BY headerId, iscacId  
                          ORDER BY paymentAmt DESC) AS Rank
    FROM
        header
)
SELECT
    headerId,
    iscacId ,
    paymentAmt 
FROM
    Ranked
WHERE
    Rank = 1
ORDER BY
    paymentAmt DESC;

Answer 1

Not sure i got your spec, but this seems to generate the same answer so:

SELECT  *
,   ROW_NUMBER() OVER(ORDER BY amtGroup DESC, HeaderId, paymentAmt DESC) AS ActualRank
FROM    (
    SELECT  *
    ,   MAX(paymentAmt) OVER(PARTITION BY HeaderID) amtGroup
    FROM    (
        VALUES  (1234, 1162, 1000, 3)
        ,   (1234, 1161, 200, 5)
        ,   (1234, 1163, 300, 4)
        ,   (1221, 1160, 900, 6)
        ,   (1221, 1165, 100, 7)
        ,   (1220, 1159, 3000, 2)
        ,   (1223, 1178, 4000, 1)
        ,   (1250, 1130, 800, 8)
    ) t (headerid,iscacId,paymentAmt,Rank)
) x

I assume every HeaderId is own group and calculate the maximum amount per such group. Then it's a simple row_number ordered by DESC amount, with HeaderId and individual payment amount as tiebreaker.