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? – Michael Vehrs Feb 14 '17 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 Feb 14 '17 at 12:00
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 Feb 14 '17 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 Feb 14 '17 at 15:52
  • That's pretty cool! I am getting the correct output now. Thank you very much @fancyPants!!! :) – peon Feb 15 '17 at 4:28

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.