Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a file like below which I have sorted based on the username field.

UserID score UserName
1234    200   Jack
5678    150   Jill
8543    200   Jill 
5678    100   John

I am trying to remove the rows which have the lowest score for the same usernames. So, I want to have the output as,

UserID score UserName
1234    200   Jack
8543    200   Jill 
5678    100   John
share|improve this question
Are the rows sorted by UserName and score? Or could the two Jill rows be in the opposite order? – Hauke Laging Mar 13 '14 at 16:31
Those 2 rows are not sorted. It is sorted only based on the username column. – Ramesh Mar 13 '14 at 16:32
Is it an option for you to make the input sorted by both columns? That would make the solution much easier (to make and to understand). – Hauke Laging Mar 13 '14 at 16:40
What if Jill has 3 rows: do you want to keep the one highest, or remove the one lowest? – glenn jackman Mar 13 '14 at 16:46
I just need to keep the highest. – Ramesh Mar 13 '14 at 16:56
up vote 4 down vote accepted

The simplest approach would be to sort on the score field instead:

$ sort -nk2 file | awk '{k[$NF]=$0} END{for (i in k){print k[i]}}'
UserID score UserName
8543    200   Jill 
1234    200   Jack
5678    100   John

Or, in perl:

sort -nk2 file | perl -ane '$k{$F[$#F]}=$_; END{print "$k{$_}" for keys(%k)}'

The -a flag for perl turns on auto splitting, basically it will behave like awk, and split each line on white space, saving the fields in the array @F. The -n means process the input file line, by line.

$F[$#F] is the last element of @F, so the last field: the username. $k{$F[$#F]}=$_; saves each line in the hash %k where the keys are the usernames, overwriting whatever was there before. Since we first sort the file, this means that $k{username} will be the highest score for that username's entry. At the end, we print each line saved in %k.

share|improve this answer
As long as you're sorting, use -r and just print the first occurrence of each name – Kevin Mar 13 '14 at 16:58
It's difficult to give testing input in a comment thus I abused an edit. The task is to delete one, your code deletes three lines. – Hauke Laging Mar 13 '14 at 16:58
@HaukeLaging yes, the OP was not clear but he actually want to keep only the highest score (which is what I had understood in the first place) so I rolled back your edit. – terdon Mar 13 '14 at 17:00
@Kevin why is that simpler? You would still need to parse to get only one line per username. Am I missing something? – terdon Mar 13 '14 at 17:05
@RahulPatil see updated answer. – terdon Mar 13 '14 at 17:41

Try this:

awk 'NR==1{print $1,$2,$3};NR!=1{if($2>a[$3]){a[$3]=$2;b[$3]=$1}}
    END{for(x in a){print b[x],a[x],x}}' OFS="\t" file

UserID  score   UserName
1234    200     Jack
8543    200     Jill
5678    100     John

Or using perl:

perl -ane '$h{$F[$#F]}=[$F[$#F-1],"$_"] if $F[$#F-1] > $h{$F[$#F]}->[0];
    END{print "$h{$_}->[1]" for keys %h}' file
share|improve this answer
Good awk solution unless Jack or John happen to have a negative score ;-) Also no guarantees that input order will be retained – iruvar Mar 13 '14 at 17:17
Yeap, if that happened, we need one more check :). – cuonglm Mar 13 '14 at 17:22
keeping one array storing $0 would be a lot simpler – glenn jackman Mar 13 '14 at 17:39

An alternate without arrays:

$ awk '
seen == $NF {line = (ishigh > $2) ? line : $0; next}
line {print line}
{seen = $NF; ishigh = $2; line = $0}
END {print line}' file
UserID score UserName
1234    200   Jack
8543    200   Jill
5678    100   John
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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