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
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
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?
Re: SQL Question
Use following query...
Select username, max(translog.changedate)
group by username
This will return userwise max changedate...
If you have still query, just send me exact structure of both the table with sample date for each table
AllianceTek @ http://www.alliancetek.com/
Re: SQL Question
I will still need to group by the gen.loan_num because I am counting the loans later on in the report
So I need to grab the username from the samerow of data where I am getting the max(changedate)
It is almost like I need to have a where clause in the join statement. example left JOIN users on users.file_id = gen.file_id where max(changedate)
Does that make sense? Let me know how to get you the sample data and I will send it over.
Thanks for the taking the time.