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

Answer 2

You could use the group by method as described above or you could just use the DISTINCT function, as below:

SELECT DISTINCT
  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;

Also, if you are going to use the MAX() function you should be able to completely remove the subquery join for maxrecord, see below:

SELECT
  b.AccountName,
  a.UserName,
  b.PrimaryGroup,
  b.MemberList,
  MAX(a.LogonDateTime) as maxLogonDateTime,
  a.UserID
 FROM UDT_UserLastActivity a
 JOIN UDT_User b
   ON b.UserID = a.UserID
 GROUP BY b.AccountName,
  a.UserName,
  b.PrimaryGroup,
  b.MemberList,
  a.UserID
 ORDER BY MAX(a.LogonDateTime) DESC;

Hope that helps.

Answer 3

I just need a list of all my users with their latest logon date.

Assuming that the basic data is in the users table and you only need the LogonDateTime from the activity table:

SELECT
   u.AccountName,  
   u.UserName,
   u.PrimaryGroup,
   u.MemberList,
   (-- Scalar subquery to get the last logon 
     SELECT MAX(LogonDateTime)
     FROM UDT_UserLastActivity AS ula
     WHERE ula.UserID = u.UserID
   ) AS LastLogon,
  u.UserID
FROM UDT_User AS u
ORDER BY LastLogon DESC