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 extract the latest updated for all these users, basically here's 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 Table GROUP BY Userid, FirstName, LastName
I still get 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
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
You aren't getting duplicates. 'Pam'
is not equal to 'Pamella'
from the perspective of the database; the fact that one is a colloquial shortening of the other doesn't mean anything to the database engine. There really is no reliable, universal way to do this (since there are names that have multiple abbreviations, like "Rob" or "Bob" for "Robert", as well as abbreviations that can suit multiple names like "Kel" for "Kelly" or "Kelsie", let alone the fact that names can have alternate spellings).
For your simple example, you could simply select and group by SUBSTRING(FirstName, 1, 3)
instead of FirstName
, but that's just a coincidence based upon your sample data; other name abbreviations would not fit this pattern.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With