(imported comment written by NoahSalzman)
Woo hoo! One of our long time product experts, John Talbert, provided me with this psychedelic bit of Relevance:
q: (it, maximum of items 1 of items 1 of (it,(
"cat", 1;
"cat", 5;
"dog", 3;
"dog", 4;
"bat", 1;
"bat", 7;
"bat", 11)) whose (item 0 of it = item 0 of item 1 of it)) of unique values of items 0 of (
"cat", 1;
"cat", 5;
"dog", 3;
"dog", 4;
"bat", 1;
"bat", 7;
"bat", 11) A: bat, 11 A: cat, 5 A: dog, 4
I then added the multiplicity part to get:
Q: (items 0 of it, items 1 of it, maximum of items 1 of items 1 of (items 0 of it, (
"cat", 1;
"cat", 5;
"dog", 3;
"dog", 4;
"bat", 1;
"bat", 7;
"bat", 11)) whose (item 0 of it = item 0 of item 1 of it)) of (it, multiplicity of it) of unique values of items 0 of (
"cat", 1;
"cat", 5;
"dog", 3;
"dog", 4;
"bat", 1;
"bat", 7;
"bat", 11) A: bat, 3, 11 A: cat, 2, 5 A: dog, 2, 4
Which led me to this solution (I hope) for your problem:
Q: (items 0 of it, items 1 of it, maximum of items 1 of items 1 of (items 0 of it, ((preceding texts of firsts
"," of following texts of firsts
"," of it, ((substring (8,2) of it &
" " & first 3 of (month (substring (5,2) of it as integer) as string) &
" " & substring (0,4) of it &
" " & substring (11,8) of it &
" -0000") of it as time) of following texts of lasts
"," of preceding texts of lasts
"," of it) of lines of file
"c:\data.txt")) whose (item 0 of it = item 0 of item 1 of it)) of (it, multiplicity of it) of unique values whose (multiplicity of it > 1) of (preceding texts of firsts
"," of following texts of firsts
"," of lines of file
"c:\data.txt") A: user1, 6, ( Mon, 10 May 2010 01:08:30 -0700 ) A: user2, 4, ( Thu, 20 May 2010 03:55:51 -0700 ) A: user2-a, 2, ( Thu, 20 May 2010 03:55:28 -0700 )
And here it is again with v7 compatible syntax, since “substring” requires v8:
Q: (items 0 of it, items 1 of it, maximum of items 1 of items 1 of (items 0 of it, ((preceding texts of firsts
"," of following texts of firsts
"," of it, ((last 2 of first 10 of it &
" " & first 3 of (month (last 2 of first 7 of it as integer) as string) &
" " & first 4 of it &
" " & last 8 of it &
" -0000") of it as time) of following texts of lasts
"," of preceding texts of lasts
"," of it) of lines of file
"c:\data.txt")) whose (item 0 of it = item 0 of item 1 of it)) of (it, multiplicity of it) of unique values whose (multiplicity of it > 1) of (preceding texts of firsts
"," of following texts of firsts
"," of lines of file
"c:\data.txt") A: user1, 6, ( Mon, 10 May 2010 01:08:30 -0700 ) A: user2, 4, ( Thu, 20 May 2010 03:55:51 -0700 ) A: user2-a, 2, ( Thu, 20 May 2010 03:55:28 -0700 )
Edit: fixed month-to-time string to work with more than just “May”