SELECT TOP 1 is returning multiple records

问题:

I shall link my database down below.

I have a query called 'TestMonday1' and what this does is return the student with the fewest 'NoOfFrees' and insert the result of the query into the lesson table. Running the query should help explain what i mean. The problem im having is my SQL code has 'SELECT TOP 1' yet if the query returns two students who have the same number of frees it returns both these records. Wit this being a timetable planner, it should only ever return one result, i shall also put the code below,

Many thanks

Code:

INSERT INTO Lesson ( StudentID, LessonStart, LessonEnd, DayOfWeek )
SELECT TOP 1 Availability.StudentID, Availability.StartTime, 
             Availability.EndTime, Availability.DayOfWeek
FROM Availability
WHERE 
  Availability.StartTime='16:00:00' AND 
  Availability.EndTime='18:00:00' AND 
  Availability.DayOfWeek='Monday' AND 
  LessonTaken IS NULL
ORDER BY 
  Availability.NoOfFrees;

回答1:

This happens because Access returns all records in case of ties in ORDER BY (all records returned have the same values of fields used in ORDER BY).

You can add another field to ORDER BY to make sure there's no ties. StudentID looks like a good candidate (though I don't know your schema, replace with something else if it suits better):

ORDER BY 
  Availability.NoOfFrees, Availability.StudentID;