selecting MAX date records
-
What am I doing wrong? select username AS UserID, domainname AS Domain, dt AS Date from testtable gives me: UserID, Domain, Date kt001, c_domain, 2/20/2006 kt001, p_domain, 2/15/2006 kt001, p_domain, 2/19/2006 which is normal. SELECT username AS UserID, MAX(domainname) AS Domain, MAX(dt) AS Date FROM testtable GROUP BY username gives me: UserID, Domain, Date kt001, p_domain, 2/20/2006 which i would want the domain of whatever the MAX date was... any clues?
-
What am I doing wrong? select username AS UserID, domainname AS Domain, dt AS Date from testtable gives me: UserID, Domain, Date kt001, c_domain, 2/20/2006 kt001, p_domain, 2/15/2006 kt001, p_domain, 2/19/2006 which is normal. SELECT username AS UserID, MAX(domainname) AS Domain, MAX(dt) AS Date FROM testtable GROUP BY username gives me: UserID, Domain, Date kt001, p_domain, 2/20/2006 which i would want the domain of whatever the MAX date was... any clues?
I think you may want to handle this as a subquery - something like this:
Select a.username as User, b.domainname as Domain, a.MaxDT as Date
From
(
Select username, Max(dt) as MaxDT
From testtable
Group By username
) a INNER JOIN testtable b ON a.username=b.username AND a.MaxDT = b.dt