-
September 10th, 2012, 12:50 PM
#1
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
-
September 11th, 2012, 07:51 AM
#2
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/
-
September 15th, 2012, 06:55 PM
#3
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|