Find greatest value for each specific ID

jrtomici · 2022-09-28T17:00:43+00:00

There was an error rendering this rich post.

Hi,

I have a dataset like this:

ID    Value
1     5
1     6
1     7
2     23
2     24
2     25

I want to write a ETL formula that creates a third column saying the greatest value for each ID is:

ID    Value    Greatest
1     5        7
1     6        7
1     7        7
2     23       25
2     24       25
2     25       25

Thank you

Find more posts tagged with

Magic ETL

Beast Mode

Formulas

Or do a group by ID, find the greatest value and join it back on.

I'd use the Rank and Window Tile

and then partition by your ID.

Or do a group by ID, find the greatest value and join it back on.

+1 @trafalger window function max() would be good except the window doesn't have an unlimited end of window.

so group by would be the best.