T-SQL - Getting most recent date and most recent future date

Question

Assume the table of records below

ID    Name       AppointmentDate
--    --------   ---------------
1     Bob         1/1/2010
1     Bob         5/1/2010
2     Henry       5/1/2010
2     Henry       8/1/2011
3     John        8/1/2011
3     John       12/1/2011

I want to retrieve the most recent appointment date by person. So I need a query that will give the following result set.

1   Bob    5/1/2010 (5/1/2010 is most recent)
2   Henry  8/1/2011 (8/1/2011 is most recent)
3   John   8/1/2011 (has 2 future dates but 8/1/2011 is most recent)

Thanks!

Answer 1

Assuming that where you say "most recent" you mean "closest", as in "stored date is the fewest days away from the current date and we don't care if it's before or after the current date", then this should do it (trivial debugging might be required):

SELECT ID, Name, AppointmentDate
 from (select
           ID
          ,Name
          ,AppointmentDate
          ,row_number() over (partition by ID order by abs(datediff(dd, AppointmentDate, getdate()))) Ranking
         from MyTable) xx
 where Ranking = 1

This usese the row_number() function from SQL 2005 and up. The subquery "orders" the data as per the specifications, and the main query picks the best fit.

Note also that:

• The search is based on the current date
• We're only calculating difference in days, time (hours, minutes, etc.) is ignored
• If two days are equidistant (say, 2 before and 2 after), we pick one randomly

All of which could be adjusted based on your final requirements.