I'm trying to write a query to identify the next item in a First In First Out system for the use of material with an expiry date.

The material will have a unique Lot Number but there maybe multiple Rolls of the material each roll has a unique roll number. All of the material in the Lot will share the same expiry date.

There may be multiple Lots of the same material with different lot numbers and Expiry dates.

The query so far reduces the records to all lots still within date but what I want is to reduce the list to One record i.e. The next Roll of material which must be used.

Any assistance will be gratefully received

---

Reply from Alex Hedley:

SELECT TOP 1 will get the first, just order it the way you need to get the single one you want.