Select users with last login (Group By)

Posted on 2008-11-13
Last Modified: 2012-05-11
I have two tables, the first stores a list of users (UsersAccounts) and the second stores every attempt the users have made to login to the system (UsersLoginAttempts). The tables have the following fields:

PK_User_ID (int)

PK_LoginAttempt_ID (int) fk from PK_User_ID
FK_User_ID (when this != 0 it was a valid login)

I am trying to return a list of users along with their last successful login.  I am trying to make the result set look like tihs:

NameFirst    NameLast   LastLoginAttempt
Jason           Andrews     9/14/2008 3:00 PM
April             O'Neil          1/28/2008 4:43 AM
Michael         Jackson       -
David           Waters        3/21/2008 9:15 PM

Notice how the user 'Michael jackson' doesn't have a time associated - this i sbecause he has never logged in.  Also, the other users could have logged in successfully 30 times or more, but it should only show their last successful login attempt.  Even if a user has never logged in successfully, it should show their name...

Can anyone help?  I can't find out how to do this...


Question by:volumeIII
    LVL 8

    Expert Comment

    Try this

    Select NameFirst,  NameLast,   DateAttempted
    from useraccounts
    left join (select fk_user_id, UserNameAttempted, DateAttempted from UsersLoginAttempts as ula
    join (select fk_user_id, max(DateAttempted) group by fk_user_id) as a on ula.fk_user_id = a.fk_user_id)


    Author Comment

    Unfortunately I couldn't get it to work... is it possible to make this happen without subqueries?  I am using a tool to do this and it doesn't allow sub queries. :(
    LVL 8

    Accepted Solution

    Not likely. If you're using something that doesn't allow a subquery (you're not able to type the sql code?), then you're probably not going to be able to use common table expressions either.

    I did have a type, well several typos in the code, you can try this instead if you want.

    Select  NameFirst,
    from    useraccounts
            left join ( ( select    fk_user_id,
                          from      UsersLoginAttempts
                        ) as ula
                        join ( select   fk_user_id,
                               FROM     UsersLoginAttempts
                               group by fk_user_id
                             ) as a on ula.fk_user_id = a.fk_user_id
                      )AS b ON pk_user_id = b.fk_user_id

    Open in new window


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now