TSQL Select Max.

Question

Userid FirstName LastName UserUpdate 1 Dan Kramer 1/1/2005 1 Dan Kramer 1/1/2007 1 Dan Kramer 1/1/2009 2 Pamella Slattery 1/1/2005 2 Pam Slattery 1/1/2006 2 Pam Slattery 1/1/2008 3 Samamantha Cohen 1/1/2008 3 Sam Cohen 1/1/2009 

I need to retrieve the latest updates for all of these users, basically here, what I'm looking for:

 Userid FirstName LastName UserUpdate 1 Dan Kramer 1/1/2009 2 Pam Slattery 1/1/2008 3 Sam Cohen 1/1/2009 

Now when I run the following:

SELECT Userid, FirstName, LastName, Max (UserUpdate) AS MaxDate From the GROUP BY Userid, FirstName, LastName table

I am still getting duplicates, something like this:

 Userid FirstName LastName UserUpdate 1 Dan Kramer 1/1/2009 2 Pamella Slattery 1/1/2005 2 Pam Slattery 1/1/2008 3 Samamantha Cohen 1/1/2008 3 Sam Cohen 1/1/2009 

Answer 1

to try:

 declare @Table table (userid int,firstname varchar(10),lastname varchar(20), userupdate datetime) INSERT @Table VALUES (1, 'Dan' ,'Kramer' ,'1/1/2005') INSERT @Table VALUES (1, 'Dan' ,'Kramer' ,'1/1/2007') INSERT @Table VALUES (1, 'Dan' ,'Kramer' ,'1/1/2009') INSERT @Table VALUES (2, 'Pamella' ,'Slattery' ,'1/1/2005') INSERT @Table VALUES (2, 'Pam' ,'Slattery' ,'1/1/2006') INSERT @Table VALUES (2, 'Pam' ,'Slattery' ,'1/1/2008') INSERT @Table VALUES (3, 'Samamantha' ,'Cohen' ,'1/1/2008') INSERT @Table VALUES (3, 'Sam' ,'Cohen' ,'1/1/2009') SELECT dt.Userid,dt.MaxDate ,MIN(a.FirstName) AS FirstName, MIN(a.LastName) AS LastName FROM (SELECT Userid, Max(UserUpdate) AS MaxDate FROM @Table GROUP BY Userid ) dt INNER JOIN @Table a ON dt.Userid=a.Userid and dt.MaxDate =a.UserUpdate GROUP BY dt.Userid,dt.MaxDate 

OUTPUT:

 Userid MaxDate FirstName LastName ----------- ----------------------- ---------- -------------------- 1 2009-01-01 00:00:00.000 Dan Kramer 2 2008-01-01 00:00:00.000 Pam Slattery 3 2009-01-01 00:00:00.000 Sam Cohen 

Answer 2

You do not get duplicates. 'Pam' does not match 'Pamella' in terms of database; the fact that one is the colloquial abbreviation of the other does not mean anything to the database engine. There is really no reliable, universal way to do this (since there are names that have several abbreviations, such as “Rob” or “Bob” for “Robert”, as well as abbreviations that can correspond to several names such as “Kel” for “Kelly” "" or "Kelsie", not to mention the fact that names can have alternative spellings).

For your simple example, you can simply select and group by SUBSTRING(FirstName, 1, 3) instead of FirstName , but this is just a match based on your sample data; other name abbreviations will not match this pattern.

Answer 3

Or use a subquery ...

 SELECT a.userID, a.FirstName, a.LastName, b.MaxDate FROM myTable a INNER JOIN ( SELECT UserID, Max(ISNULL(UserUpdate,GETDATE())) as MaxDate FROM myTable GROUP BY UserID ) b ON a.UserID = b.UserID AND a.UserUpdate = b.MaxDate 

A subquery (named "b") returns the following:

 Userid UserUpdate 1 1/1/2009 2 1/1/2008 3 1/1/2009 

The INNER JOIN between the subquery and the source table allows you to filter the source table to match only records - that is, only records with the UserID / UserUpdate pair that matches the UserID / MaxDate pair from the subquery will be returned, giving you the unshakable result set that you were looking for:

 Userid FirstName LastName UserUpdate 1 Dan Kramer 1/1/2009 2 Pam Slattery 1/1/2008 3 Sam Cohen 1/1/2009 

Of course, this is just a workaround. If you really want to solve the problem for the long term, you should normalize the original table by dividing it into two parts.

Table1:

 Userid FirstName LastName 1 Dan Kramer 2 Pam Slattery 3 Sam Cohen 

Table2:

 Userid UserUpdate 1 1/1/2007 2 1/1/2007 3 1/1/2007 1 1/1/2008 2 1/1/2008 3 1/1/2008 1 1/1/2009 2 1/1/2009 3 1/1/2009 

This would be a more standard way of storing data, and it would be much easier to query (without having to resort to a subquery). In this case, the request will look like this:

 SELECT T1.UserID, T1.FirstName, T1.LastName, MAX(ISNULL(T2.UserUpdate,GETDATE())) FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.UserID = T2.UserID GROUP BY T1.UserID, T1.FirstName, T1.LastName 

Answer 4

Another alternative, if you have SQL 2005 (I think?) Or later, would use the Common Table Expression expression and pull the user id and maximum date from the table, and then join to get the matching first and last name for the maximum date. NOTE. It is assumed that the userid + date will always be unique, the request will break if you get two lines with the same user ID and date. As already noted, this is a pretty terrible database design, but sometimes it’s a life issue, the problem still needs to be solved. eg.

 declare @Table table (userid int,firstname varchar(10),lastname varchar(20), userupdate datetime) INSERT @Table VALUES (1, 'Dan' ,'Kramer' ,'1/1/2005') INSERT @Table VALUES (1, 'Dan' ,'Kramer' ,'1/1/2007') INSERT @Table VALUES (1, 'Dan' ,'Kramer' ,'1/1/2009') INSERT @Table VALUES (2, 'Pamella' ,'Slattery' ,'1/1/2005') INSERT @Table VALUES (2, 'Pam' ,'Slattery' ,'1/1/2006') INSERT @Table VALUES (2, 'Pam' ,'Slattery' ,'1/1/2008') INSERT @Table VALUES (3, 'Samamantha' ,'Cohen' ,'1/1/2008') INSERT @Table VALUES (3, 'Sam' ,'Cohen' ,'1/1/2009'); with cte ( userid , maxdt ) as (select userid, max(userupdate) from @table group by userid) SELECT dt.Userid, dt.firstname, dt.lastname, cte.maxdt FROM @Table dt join cte on cte.userid = dt.userid and dt.userupdate = cte.maxdt 

Exit

 Userid firstname lastname maxdt ----------- ---------- -------------------- ----------------------- 3 Sam Cohen 2009-01-01 00:00:00.000 2 Pam Slattery 2008-01-01 00:00:00.000 1 Dan Kramer 2009-01-01 00:00:00.000