extract records with the smallest value per group

I have a table containing the following

  Acct #       Collection Date/Time       Result Date/Time       Clinical Event
000005        1/1/2014 00:10:00           1/1/2014 00:42:00     Troponin-T
000005        1/1/2014 00:15:00           1/1/2014 00:20:00     Troponin-I
000005        1/1/2014 01:30:00           1/1/2014 02:10:00     Troponin-T
001002        1/3/2014 23:59:00           1/4/2014 00:45:00     Troponin-T
001002        1/4/2014 05:20:00           1/4/2014 05:25:00     Troponin-I

I need a query that would pull the entire record with the earliest result time for each account.  

For Acct # 0005, I need the record with result date/time of 1/1/2014 00:20:00 (2nd rec)
For Acct # 1002, I need the record with result date/time of 1/4/2014 00:45:00 (4th rec)
ddschreck01Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this query


 Select A.*
 From Tablex as A
 Inner join
 (Select min(B.[Result Date/Time]) as MinResult, B.[Acct #]
  from TableX as B
  Group by B.[Acct #]
  ) As C
  On A.[Acct #]= C.[Acct #] And A.[Result Date/Time]=C.[MinResult]
0
 
ddschreck01Author Commented:
Thank you very much - that worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.