Left only MAX value per group

Question

I have got table like this:

TransactionID	…	Cost	MaxCostPerGroup
1234	...	1550	1550
2342	...	1950	2000
2342	...	2000	2000
4444	...	600	600
4444	...	400	600
4444	...	500	600

TransactionID – not unique
… - a lot of columns (30+)
Cost – could be different to one TransactionID
MaxCostPerGroup column shows max value for each TransactionID.

To continue working with data I need to bring table to the following form:

TransactionID	…	Cost	MaxCostPerGroup
1234	...	1550	1550
2342	...	1950	null
2342	...	2000	2000
4444	...	600	600
4444	...	400	null
4444	...	500	null

Then I want to sum MaxCostPerGroup by date (for example). Problem is that I must save every row, I cannot just group by. In ‘…’ section a lot of unique information, that is why I want to left only one value per TransactionID in last column. How can I do it with SQL?
Many thanks.

Answer 1

Using your data, I got the max value by partitioning by TransactionId and adding a IF statement to add NULLs.

See query below:

WITH sample_data as(
    select '1234' as TransactionID, 1550 as Cost, 1550 as MaxCostPerGroup,
    union all select '2342' as TransactionID, 1950 as Cost, 2000 as MaxCostPerGroup,
    union all select '2342' as TransactionID, 2000 as Cost, 2000 as MaxCostPerGroup,
    union all select '4444' as TransactionID, 600 as Cost, 600 as MaxCostPerGroup,
    union all select '4444' as TransactionID, 400 as Cost, 600 as MaxCostPerGroup,
    union all select '4444' as TransactionID, 500 as Cost, 600 as MaxCostPerGroup
),
get_max as (
    select TransactionId,
        Cost,
        max(MaxCostPerGroup) OVER (PARTITION BY TransactionId) as max_per_id
    from sample_data
),

add_null as (
    select TransactionId,
        Cost,
        max_per_id,
        if (Cost = max_per_id, max_per_id, NULL) as MaxCostPerGroup
    from get_max
)

select TransactionId,Cost,MaxCostPerGroup from add_null

Output: