-
SQL Question
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
-
Re: SQL Question
Use following query...
Select username, max(translog.changedate)
from translog
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
Regards,
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.
Jake