SQL Lastest Date per User - Eliminating Duplicates

Question

I have looked over 30 different ways to get the latest date from a joined table. Need help from the pros.

Note:
- Using SolarWinds to create custom SQL reports
- Fairly new to SQL

I am trying to get a list of all my users with their username, primary group, name, and lastest logon date. This data is spread out between two tables.

The issue is that the current code I have is giving me a long list of duplicate users with the same date. I just need a list of all my users with their latest logon date.

This is the code that I came up with, it creates my report with no problem but still shows me duplicates, there is no grouping at all. What can i be missing?

SELECT
b.AccountName,
a.UserName,
b.PrimaryGroup,
b.MemberList,
a.LogonDateTime,
a.UserID

FROM UDT_UserLastActivity a

JOIN (
      SELECT UserID, MAX(LogonDateTime) maxLogon
      FROM UDT_UserLastActivity
      GROUP BY UserID
      ) maxRecord
ON maxRecord.UserID = a.UserID
AND maxRecord.maxLogon = a.LogonDateTime

JOIN UDT_User b
ON b.UserID = a.UserID

ORDER BY a.LogonDateTime DESC

Thanks in advance for anyone and everyone willing to help!

Answer 1

just group by and search for max

SELECT
b.AccountName,
a.UserName,
b.PrimaryGroup,
b.MemberList,
max(a.LogonDateTime) as LastDate,
a.UserID

FROM UDT_UserLastActivity a

JOIN (
      SELECT UserID, MAX(LogonDateTime) maxLogon
      FROM UDT_UserLastActivity
      GROUP BY UserID
      ) maxRecord
ON maxRecord.UserID = a.UserID
AND maxRecord.maxLogon = a.LogonDateTime

JOIN UDT_User b
ON b.UserID = a.UserID
group by   
b.AccountName,
a.UserName,
b.PrimaryGroup,
b.MemberList,
a.UserID
ORDER BY a.LogonDateTime DESC