Find the maximum value of Column3 and print the values of Column1 and 2 only

Question

In Unix, I am trying to find a command that would find the maximum value in Column3 and print the corresponding values from Column2 and Column1 (but not from Column3) in a new file.

 Column1     Column2     Column3
   A          1          25
   B          2          6
   C          3          2
   D          4          16
   E          5          10

What should be the Unix command? Should I use grep or awk or datamash?

Answer 1

I would use awk. Assuming that the data is formatted exactly as per your sample data, the following will produce the desired output:

awk -v MAX=0 '{ if(NR>1 && $3>MAX){WANT1=$1; WANT2=$2; MAX=$3}} END{print WANT1, WANT2}' infile > outfile

Answer 2

Assuming that your data is formatted exactly as stated by you (this goes especially for the 3rd column, numbers all left aligned) and that this data is provided by cating a file, you could try this command:

*input generating command* | sort -nrk3 | awk '{print $1 " " $2}' > output_file

In case your data is provided by some other command or source, change cat to that other command. For more information see this post which I basically just tailored to your needs.

Edit:

I changed the above command slighty as there's no need to tell sort to start from column 3 character 1, since sort always treats a blank as a separator. One can of course give the input file straight to sort as well, as a user pointed out. If also the headers are part of your data, change the above command to:

*input generating command* | sort -nrk3 | awk '{if (NR>1) print $1 " " $2}' > output_file

Answer 3

awk '{if(NF>2 && $3>M){M=$3;print $1,$2} }' file1 | tail -1 > file2