I have a text file (with header line) called file.txt. I am trying to extract the lines equal to the maximum value of a specific column (I do not know what the max value is):

ID t1 q1 t2 q2 q3 
1 f 45 ex 1 45
2 r 47 tr 1 33
3 r 33 ex 2 44
4 f 44 s 0 55
5 e 32 ex 0 54
6 f 34 tr 2 46

I need to find the maximum value of column $5 and then print only the lines with column 5 equal to this number:

3 r 33 ex 2 44
6 f 34 tr 2 46

I think the following code works, but my file is huge and it takes a vary long time, so I am looking for a faster solution (maybe using sort?):

This is what I have for now:

Fist find the max value:

max=`awk '{print $5}' file.txt | sort -nr | sed -n 2p`

Then select the lines where column 5 is equal to this value:

awk 'NR>1' file.txt|while read LINE; do value=`echo $LINE|awk '{print $5}'`; if [ $value -eq $max ]; then echo $LINE >> test.txt; fi; done

One way of doing it would be to read the file once to get max and then again to print the relevant lines:

max=$(awk 'NR>1 && $5>max {max=$5}; END{print max}' file.txt) && 
 awk -v max="$max" '$5==max' file.txt 

Or, more concisely:

awk -v m="$(awk '(NR>1 && $5>m){m=$5};END{print m}' file.txt)"  '$5==m' file.txt 

The trick here is awk's -v flag which allows you to pass a variable to awk. In this case, I am first calculating the max value and then giving it to awk as variable max.

  • Thank you! All these work, I just accepted the first code since it was more in line with what I had tried so I understand it better. Thank a lot to all!! – user971102 Sep 10 '13 at 14:29

This is a fairly typical problem for which an idiomatic awk solution involving two passes over the file exists. In the first pass, identify the max value for $5 and in the second, extract records that contain that max value in $5. Here's a quick example.

awk 'NR == FNR && NR > 1{max = max < $5? $5: max; next}; $5 == max{print}' file.txt file.txt
  • Thank you! Both these methods work, I find this a bit hard to understand because I am a beginner…I am having a hard time making sense mostly of the piece inside brackets (and why do you repeat the input file twice?). But it does the job perfectly... – user971102 Sep 10 '13 at 14:32
  • @user971102, sure. Since terdon has done a great job of providing a clear explanation, you should accept his answer – iruvar Sep 10 '13 at 14:43

If memory usage is not really an issue, a one-pass version in perl would probably be:

perl -ane 'END { $"=""; print "@res"; } if($F[4] =~ /^\d+/ and $F[4] > $max) { 
    $max = $F[4]; @res = (); } push @res, $_ if($F[4] =~ /^\d+$/ and $max == $F[4]);' infile

-n tells perl to process the infile one line at a time passing each line to the commands specified in -e. The -a tells perl to expand each line around the field separator (this defaults to space), and assign this to an array called @F. The upshot is for every line we can process it and use $F[n] to refer to the nth element of that line.

The perl itself:

END { $"=""; print "@res"; } # at the end of execution set the field separator to
                             # empty and print the contents of @res, which includes
                             # newlines when the matching rows were stored

if($F[4] =~ /^\d+/ and $F[4] > $max) {  # if the 5th element of the line is solely a
    $max = $F[4]; @res = ();            # number and it's greater than $max (which
}                                       # starts as undefined), set $max to this number
                                        # and empty the @res results array.

push @res, $_                                  # push this line to @res ...
     if($F[4] =~ /^\d+$/ and $max == $F[4]);   # IF the 5th element is solely a 
                                               # number and equal to $max

The logic is that $max starts as undefined and the @res array empty. The first time we find a number in column 5, we store this in $max and empty @res. This also has the side effect of emptying @res if we find a new maximum value in column 5 on following rows. As a separate check, if column 5 is equal to $max add this line to @res (store lines with the present maximum value). Repeat for all lines, and then the END { } block is executed which prints the contents of the @res results array without the leading space field separator normally set in $".

This could also probably be achieved in awk, but my awk-fu is not that great!


Sort the file with values in descending order, then print the lines from the top until the value changes.

sort -k 5n | awk 'NR==1 {max=$5} $5!=max {quit} {print}'

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.