I have a DB where the 'translog' table has a many-to-one relationship with 'gen'. I am trying to get the 'UserName' to pull from the corresponding line that is giving me the
'max(translog.changdate)'

DB Data looks like:
Row# UserName changdate
1 MARY 9/6/2012 9:21:14 AM
2 SAMY 9/6/2012 10:19:58 AM
3 MARY 9/6/2012 5:51:16 PM
4 MARY 9/6/2012 3:55:45 PM
5 MARY 9/6/2012 3:55:50 PM
6 MARY 9/7/2012 7:15:06 AM
7 MARY 9/7/2012 7:15:35 AM
8 MARY 9/7/2012 7:16:40 AM
9 JOHNL 9/7/2012 9:17:51 AM




select
max(gen.loan_num) as loan_num,
max(translog.changdate) as changedate,
max(users.username) as UserName
from dmd_data.dbo.translog translog
left join gen on gen.file_id = translog.file_id
left JOIN users on users.file_id = gen.file_id
group by gen.loan_num


So what I am getting is the changdate from row 9, and the username from row 2. I understand why this is happening, but what I want is username from row 9. Basically I want the username to be pulled from whatever line is pulling the max(translog.changdate). How do I change my qry to pull this?

Thanks guys,
jake