# Get the maximum value of all columns 1-3 with common column 4

Given an input txt file:

``````\$ cat input.txt
-1 23 34 cheese
34 20 15 cheese
-4 -4 20 tomato
13 -3 14 tomato
4  6   7 tomato
-3 -3 -3 potato
``````

I can uniqify the data based on column 4, retaining the record with maximum value in column 1 using:

``````\$ cat input.txt | sort -k4 -k1,1rn | uniq -f3
``````

Which gives:

``````34 20 15 cheese
13 -3 14 tomato
-3 -3 -3 potato
``````

I however want to uniqify the data on column 4 and retain the maximum value in all records with the same column 4. Something like:

``````34 23 34 cheese
13 6 20 tomato
-3 -3 -3 potato
``````

Any quick way to achieve this?

Using AWK:

``````#!/usr/bin/awk -f
!keys[\$4] { keys[\$4] = 1; for (i = 1; i < 4; i++) max[\$4][i] = \$i }
{ for (i = 1; i < 4; i++) {
if (max[\$4][i] < \$i) max[\$4][i] = \$i
} }
END { for (key in max) print max[key][1], max[key][2], max[key][3], key }
``````

This tracks the three maximum values for each key in column four.

There is one caveat: the order of keys isn't maintained.

• This solves caveat 1 I guess: #!/usr/bin/awk -f !keys[\$4] { keys[\$4] = 1 for (i = 1; i < 4; i++) {max[\$4,i] = \$i } } { for (i = 1; i < 4; i++) { if (max[\$4,i] < \$i) max[\$4,i] = \$i } } END { for (key in keys) print max[key,1], max[key,2], max[key,3], key } – Ryan Dsouza Feb 2 at 15:53
• It does indeed, I’ll edit my answer. The point of initialising the array the first time round is so that negative values are handled correctly; a missing value is interpreted as 0. – Stephen Kitt Feb 2 at 18:42