1

I have the following data:

AD12      Ar.1       352
AD12      Ar.7       495
AD12      Ar.8       491
AD12      Ar.6       495
AD12      Ar.5       495
AD12      Ar.9       491
AD13      Ar2.1      495

I want to select the rows with the highest value based on the third column for the unique ids in the first column, but maintain the duplicates for the highest value for each id in the first column, for example:

AD12      Ar.7       495
AD12      Ar.6       495
AD12      Ar.5       495
AD13      Ar2.1      495

I am not sure how to do it, I tried different sort commands, for example: sort -r -k3,3 -k1,1, but did not work. Any suggestions?

  • Are there tabs or spaces in the input file? – Jaroslav Kucera Nov 8 '17 at 15:59
  • Yes, there are tabs. – Paul Nov 8 '17 at 16:03
1

sort + awk solution:

sort -k1,1 -k3,3nr <file | awk '!a[$1]{ a[$1]=$3 }$3==a[$1]'
  • sort -k1,1 -k3,3nr - sort lines by the 1st field(regular sort) and 3rd field(numerically in reversed order)

  • !a[$1]{ a[$1]=$3 }$3==a[$1] - for each unique id $1 check if the current record has the same maximum value $3


The output:

AD12      Ar.5       495
AD12      Ar.6       495
AD12      Ar.7       495
AD13      Ar2.1      495

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.