Jump to content

Join table and get only one row from other table


Go to solution Solved by Barand,

Recommended Posts

This seems like something I've done several times a year for years, but I can't seem to get it. I can't find a solution on Google that works, although the question was asked many times and answered, too. Also, I'm low on sleep right now.


Let's say I have two tables:




I want to join the tables and get one row per user, with only the latest login row. Assume "UserId" is the pk/fk and there's some sort of LoginTime in the USERS_LOGINS table.


So I will get results like:

- Brian  8/4

- Mitch 8/3

- Jerry 8/2


I appreciate the help.

Link to comment
Share on other sites

Create a dynamic table with the user_id and last login time for each user. Use that table as an intermediary to join each user record with the user last login record.



SELECT u.*, ul.*
FROM users u
    SELECT user_id, MAX(login_time) as last_login
    FROM user_logins
    GROUP BY user_id
    ) as i 
  ON i.user_id = u.user_id
JOIN user_logins ul
 ON ul.user_id = i.user_id
    AND ul.login_time = i.last_login
Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.