Max based on Date/Time

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Max based on Date/Time

Post by jstevens »

I'm having a challenge generating the following results in the picture below. I know how to write SQL code for Source, Max(DateTimeEnded) as "MaxDate" but don't know how to join the ElapsedTime to the MaxDate.
EL_76.png
Your suggestions are appreciated.
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Max based on Date/Time

Post by HansV »

You can do this using a subquery:

Code: Select all

SELECT Source, DateTimeEnded, ElapsedTime 
FROM [tablename]
WHERE DateTimeEnded = 
   (SELECT Max(T.DateTimeEnded) 
    FROM [tablename] AS T
    WHERE T.Source = [tablename].Source)
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Max based on Date/Time

Post by jstevens »

Thanks Hans!
Regards,
John