Grouping and ranking issue: based on table shown, script or stor

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

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

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

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;

Answers

You're close with your attempt, but you need to adjust the partitioning and ordering logic to meet the requirements for ranking based on different scenarios. You can achieve this by partitioning the data based on different conditions. Here's the adjusted query to achieve the desired ranking:

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

This query partitions the data by headerId only, and then assigns a rank to each row within each partition based on the payment amount. This will provide the desired ranking according to the rules you've specified.