How to select a Record having closer Date

[Mota]

Hi;
I have a table having a non-unique field named dID and another field named
ExpiryDate.Assuming 5 records have a same dID but a different ExpiryDate,how
to select the record having nearest ExpiryDate from this 5 Records,using SQL
?
Thank you in advance.

 

[Mike S.]

SELECT Top 1 * FROM MyTable WHERE dID=12345 ORDER BY ExpiryDate

or ORDER BY ExpiryDate DESC depending on how you defined Expiration date.

 

[Mota]

Thank you for ur help;
And as another question
if we want to know how many days(or months) we have,to reach to that
ExpiryDate,which function is more efficient and what u recommend?
Thanx again.

 

[Mike S.]

SELECT Top 1 *, DateDiff("d", ExpiryDate, Now()) As NumOfDays, DateDiff("m",
ExpiryDate, Now()) As NumOfMons
FROM MyTable WHERE dID=12345 ORDER BY ExpiryDate

 

[Mota]

Thank you so so much.

SELECT Top 1 *, DateDiff("d", ExpiryDate, Now()) As NumOfDays, DateDiff("m",
ExpiryDate, Now()) As NumOfMons
FROM MyTable WHERE dID=12345 ORDER BY ExpiryDate