Back (Current repo: scraps)

find_max_date_per_group.sh (514B)

#!/bin/bash
#Select id and datetime field, convert datetime to unix timestamp, find max value per group and print.

echo "SELECT id,date FROM <schema>.<table>;" | mysql -u root -A | \
  sed 's/\t/,/g' | \
  awk 'BEGIN{FS=","; OFS=","} (NR>1) {
    split($2, a, "[- :]");
    print $1 "," mktime(a[1]" "a[2]" "a[3]" "a[4]" "a[5]" "a[6]);
  }' | \
  awk 'BEGIN{FS=","; OFS=","} {
    c[$1]++;
    max[$1] = (!($1 in max) || $2 > max[$1]) ? $2 : max[$1];
  } END {
    for(i in c) {
      print i, max[i];
    }
  }'