SQL - SUM but filter the 2 max column

Question

I have a table like below.

qid  segment task    before  after   diff
------------------------------------------
1       0    aaa       5     5       0
1       0    aaa       5     5       0
1       1    aaa       15    15      0
1       1    aaa       10    5      10
1       0    aaa       10    5       5
3       0    bbb       10    4       6

For each task I need to sum the before,after and diff values.But the I want to do this operation only on the High qid and segment.

For eg: for task A,

• High qid = 1
• High segment =1

This is row I want to take for task A.

For task B,

• High qid = 3
• high segment = 0

The last row is the one I want to calculate.

Expected output:

task    before  after   diff
-----------------------------
aaa       25    20      10
bbb       10    4       6

Answer 1

Try with dense_rank. here is the demo.

with cte as
(
    select
        *,
        dense_rank() over (partition by task order by qid desc, segment desc) as rnk
    from myTable
)

select
    task,
    sum(before) as before,
    sum(after)  as after,
    sum(diff) as  diff
from cte
where rnk = 1
group by
    task

outoput:

| task | before | after | diff |
| ---- | ------ | ----- | ---- |
| aaa  | 25     | 20    | 10   |
| bbb  | 10     | 4     | 6    |