[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select users with last login (Group By)

Posted on 2008-11-13
3
Medium Priority
?
276 Views
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:

UsersAccounts
------------------
PK_User_ID (int)
NameFirst
NameLast

UsersLoginAttempts
-------------------------
PK_LoginAttempt_ID (int) fk from PK_User_ID
UserNameAttempted
DateAttempted
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...

Thanks,

br
0
Comment
Question by:volumeIII
  • 2
3 Comments
 
LVL 8

Expert Comment

by:i2mental
ID: 22955475
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)


0
 

Author Comment

by:volumeIII
ID: 22955551
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. :(
0
 
LVL 8

Accepted Solution

by:
i2mental earned 1500 total points
ID: 22955633
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,
        NameLast,
        DateAttempted
from    useraccounts
        left join ( ( select    fk_user_id,
                                UserNameAttempted,
                                DateAttempted
                      from      UsersLoginAttempts
                    ) as ula
                    join ( select   fk_user_id,
                                    max(DateAttempted)
                           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

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

834 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