1

Input

aaaaa|bbbbb|ccccc|ddddd|5|5|4
aaaaa|bbbbb|ccccc|ddddd|10|10|1
wwwww|xxxxx|yyyyy|zzzzz|5|5|2
wwwww|xxxxx|yyyyy|zzzzz|10|10|2

Output

aaaaa|bbbbb|ccccc|ddddd|5|5|
wwwww|xxxxx|yyyyy|zzzzz|10|10|

Used for index are:

1st to 4th column(e.g. aaaaa|bbbbb|ccccc|ddddd)

The logic for the output are the following:

  1. Compare last column of records with same index, the record with the higher last column value will be printed to the output.

  2. Compare last column of records with same index, if they are equal, the record with the greater value for columns 5 and 6 will be printed to the output.

2
  • So, what have you tried so far? Commented Feb 14, 2017 at 10:18
  • I am thinking of using awk. I just have no idea how to compare rows with the same index in one file.
    – peon
    Commented Feb 14, 2017 at 12:00

1 Answer 1

2

You can simply do it with sorting accordingly and then picking the first entry for each group.

sort filename -t'|' -k1,4 -k7rn -k5,6rn | sort -t'|' -k1,4 -u

Result:

aaaaa|bbbbb|ccccc|ddddd|5|5|4
wwwww|xxxxx|yyyyy|zzzzz|10|10|2

What this does is

  • -t'|' set the field delimiter to |
  • -k1,4 order by the first 4 fields
  • -k7rn then by the seventh field in reverse and numeric comparison
  • -k5,6rn then the same with the fifth and sixth field
  • | pipe this to another sort
  • -k1,4 -u the -u stands for uniqueness of the first 4 fields

So the whole operation is like ordering the file as you want it, so that your desired row is displayed first in groups of the first 4 fields. Then just grab the first row for each group.

3
  • Hi fancyPants. Thank you for your inputs. However, I am not looking to sort the rows with respect to their column. I would like to write the output of the said logic to another file.
    – peon
    Commented Feb 14, 2017 at 11:57
  • @peon It does exactly that. The procedure as described here is for example often used in database queries, too, like select price as max_price from products order by price desc limit 1; Don't be confused because of the sorting. It's sorting only, so that it can select (in the second sort with the -u flag) the first entry of each group. And the first entry for each group is determined by the sorting. I hope it's a bit better to understand now.
    – fancyPants
    Commented Feb 14, 2017 at 15:52
  • That's pretty cool! I am getting the correct output now. Thank you very much @fancyPants!!! :)
    – peon
    Commented Feb 15, 2017 at 4:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .