Getting max value of specific fields with awk

Shell Programming and Scripting
1

Hello All,

Here is am trying to get maximum value of third field depending on
first,second and fourth fields with awk command . delimeter is pipe(|) .

input

0221|09|14.25|aaa
0221|09|44.27|aaa
0221|09|44.33|aaa
0221|09|44.53|bbb
0221|09|34.32|bbb
0221|09|37.13|bbb
0221|09|33.68|ccc
0221|09|32.38|ccc
0221|09|33.48|ccc

output

0221|09|44.33|aaa
0221|09|44.53|bbb
0221|09|33.68|ccc
2

What have you tried to solve this problem?

Are all of the records that have common values for fields 1, 2, & 4 always adjacent as in your sample input file?

If the records with common values for fields 1, 2, & 4 are always adjacent, does the order of lines in the output matter?

3

Dear Don,
Please find my answer :
1) I have tried like this

egrep "0221|09|ccc" test.unl  | awk  -F "|"  'maxvalue=="" || $3 > maxvalue { maxvalue=$3 } END { print "ccc|"maxvalue }'

With this I am providing unique value for fields 1,2 and 4 .

2 ) Yes , all the fields have common value .

3 ) Output order does not matter . In output , line having max third field have to be displayed .

0221|09|aaa| ,44.33
4

Try this Third field move to last, if required in order can be done

awk -F"|" '(!a[$1"|"$2"|"$4]){a[$1"|"$2"|"$4]=$3;next}{if(a[$1"|"$2"|"$4] < $3){a[$1"|"$2"|"$4]=$3}}END{for( i in a) { print i"|"a}}' file_name
1 Like
5

Try also

awk -F"|" '{if ($3 > MX[$1 FS $2 FS $4]) MX[$1 FS $2 FS $4] = $3} END {for (m in MX) print m FS MX[m]}' file
0221|09|ccc|33.68
0221|09|aaa|44.33
0221|09|bbb|44.53

OR, if you want to keep the field order in the output:

awk -F"|" '{if ($3 > MX[$1 FS $2 FS FS $4]) MX[$1 FS $2 FS FS $4] = $3} END {for (m in MX) {sub ("\|\|", FS MX[m] FS, m); print m}}' file
0221|09|33.68|ccc
0221|09|44.33|aaa
0221|09|44.53|bbb
1 Like
6

Dear RudiC and Raj ,

Thanks a lot . Both code are working for me .