CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: SQL Question

  1. #1
    Join Date
    May 2012
    Posts
    7

    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

  2. #2
    Join Date
    Aug 2011
    Posts
    2

    Thumbs up 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/

  3. #3
    Join Date
    May 2012
    Posts
    7

    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
  •  





Click Here to Expand Forum to Full Width

Featured