While datamash is one the most useful packages, with awk and others, available in Linux, it lacks something called window functions.
In MySQL and other databases this function solve a problem which is not solvable with datamash. For example:
table demo
+------+--------+--------+
| cic | npanxx | rate |
+------+--------+--------+
| 0222 | 954443 | 0.0125 |
| 0222 | 954444 | 0.0135 |
| 0555 | 954443 | 0.0125 |
| 0555 | 954444 | 0.0185 |
| 0913 | 954443 | 0.0145 |
| 0913 | 954444 | 0.0045 |
| 5102 | 954443 | 0.0155 |
| 5102 | 954444 | 0.0155 |
we need to arrive a the CIC code that offers the lowest rate for each npanxx.
in MySQL, it can be done like this
select npanxx,rate,cic from (select *, rank() over (partition by npanxx order by rate ASC) as rank from demo) as a where rank=1 GROUP BY npanxx;
+--------+--------+------+
| npanxx | rate | cic |
+--------+--------+------+
| 954443 | 0.0125 | 0222 |
| 954444 | 0.0045 | 0913 |
+--------+--------+------+
With datamash we can get the min rate, but there is no way to know what CIC is the provider of that rate. So by adding window functions, we would not need to use a database to build Least Cost Routing tables, etc.
Philip Orleans