I have the following file:


I need an awk command that print out the maximum value of $21 for $18.

the desired output will look like:


I got this result, but using the sort command, as below:

sort -t, -k18,18n -k21,21nr | awk -F"," '!a[$18]++'

while I am looking to do it with single awk command.

Please advice,

  • I rolled back your edit because it changed what the question was asking for, making all answers obsolete. If you have a new question, please post it separately. – terdon Jul 29 '15 at 12:15

I don't see why you would want to do it in a single awk command, what you have seems perfectly fine. Anyway, here's one way:

$ awk -F, '(max[$18]<$21 || max[$18]==""){max[$18]=$21;line[$18]=$0}
            END{for(key in line){print line[key]}}' file

The idea is very simple. We have two arrays, max has $18 as a key and $21 as a value. For every line, if the saved value for $18 is smaller than $21 or if there is no value stored for $18, then we store the current line ($0) as the value for $18 in array line. Finally, in the END{} block, we print array line.

Note that the script above treats $18 as a string. Therefore, 001 and 1 will be considered different strings.

  • Note this won't work for negative numbers. – 123 Jul 29 '15 at 14:20
  • @User112638726 how so? If the greatest number is negative, that will be kept. Do you mean that it doesn't compare absolute numbers? That it doesn't consider -10 to be greater than 5? Why should it? – terdon Jul 29 '15 at 14:37
  • Max[$18] begins at null or 0, so if the $21 is negative the block won't be run. – 123 Jul 29 '15 at 14:52
  • @User112638726 ah, very good point! I hadn't considered that. Thanks, fixed now. – terdon Jul 29 '15 at 14:55
  • 1
    @User112638726 Damn. Good point, again :). I assume you meant max[$18]=="" not max[$18]!="". – terdon Jul 29 '15 at 15:02

Using uniq instead awk can be quicker a little:

sort -t, -k18,18nr -k21,21nr | uniq -s39 -w4
  • I just tested using a 287M file (the content of the OP's example repeated a few thousand times) and my awk approach took 23 seconds while the sort/uniq took 30. Here, both sort and uniq need to go over the entire file. Why would you expect it to be faster? Also, limiting the number of characters compared is clever but assumes that the number of characters per line is fixed, right? It will fail if one of the fields isn't empty or has a value with >1 digit. – terdon Jul 29 '15 at 11:40
  • @terdon - is your /tmp a tmpfs? – mikeserv Jul 29 '15 at 12:01
  • 1
    @terdon You make incorrect example by «content of the OP's example repeated a few thousand times» so you have 3 members in array only. For real huge amount of different data awk even can reach full of memory error. – Costas Jul 29 '15 at 12:46
  • 2
    @terdon I have write «can be quicker» because it strongly depends of data. If you'd like I'l add «in some cases». As noted by @mikeserv above awk read data twice: fist when put data into 2 arrays, then read array in end loop. So if resulting array is small — awk will be quicker. – Costas Jul 29 '15 at 13:09
  • 1
    @Costas first I want to clarify that I'm just trying to learn here. I'm not saying you're wrong, as far as I can tell, you know far more about this sort of thing than I. And, as it turns out, I'm quite right and you do know more than I do! I tried again with a file where every $18 was different and awk took 5.7 seconds while sort/uniq took 2.8 to produce the same file! – terdon Jul 29 '15 at 13:26

You can try following awk:

awk -F"," '{ if (max[$18] < $21) { max[$18] = $21; x[$18] = NR; } z[NR] = $0; } END { for (i in x) print z[x[i]]; }' file

It uses 3 arrays max and x with keys of column $18 and z with keys row numbers . In max we hold max values, in x we are holding number of row containing max value, and in z every row in file. In the END block for every key in array x we print value of z[x[i]].
This solution is not good for large files since it reads entire file into memory.


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.