Get lines with maximum values in the column using awk, uniq and sort

Question

I have a file with next format

2011-12-01 user1 access1
2011-12-01 user1 access2
2011-12-01 user2 access2
2011-12-01 user4 access2
2011-12-02 user1 access1
2012-01-01 user3 access1
2012-01-01 user4 access2

I would like to have an output that for every user shows the last date, so

2011-12-02 user1 access1
2011-12-01 user1 access2
2011-12-01 user2 access2
2012-01-01 user3 access1
2012-01-01 user4 access2

I tried something like this:

less myfile.txt | sort -k1r | uniq -f 1  | sort -b -k1

but it isn't seems to work right.

Thank you for help!

Answer 1

I think you want

cat myfile.txt| sort -k1 -r | sort --unique --stable -k2,3

(see my comment regarding cat above). The first sort will put the newest dates to the top. The second sort will sort by user+access, but, by giving --stable, will keep the previous order of lines that have the same user+access combination, i.e. newest still on top. Giving --unique, only the first line of a run with equal user+access combination is shown. (You can replace it with | uniq -f1, I'd think, if it happens to be a GNU extension your sort doesn't have.)

Answer 2

You are almost there, just need to adjust the sort options:

sort -k2,2 -k1,1r myfile.txt | uniq -f1

That is: sort by user and reverse date, leave only first appearance of user + access.

Answer 3

If you want to do it just with a single call to awk

awk '($1 > a[$2,$3]){a[$2,$3]=$1}END{for(x in a){split(x,b,SUBSEP);print a[x],b[1],b[2]}}' in