The goal is to find passengers in a flight database who accumulated 20,000 or more miles. I am asked to find the 10th passenger with the highest total miles. First I needed to create the top 10 list using the TOP function, and then from there find the 10th passenger. The code I used to find the top 10 is below, and the 10th passenger on my list is the correct passenger/total miles, but I can’t seem to figure out how to only show that passenger instead of the whole list. Maybe something in my original code is wrong, but everything I try to add to the code to only show the 10th passenger gives me an error message. I was told using the NOT IN function is a good way to go, since you know that the 10th passenger is NOT IN the top 9, but everytime I try to add the NOT IN function to my code I get a syntax error message. Can anyone help explain how to use the NOT IN function here to only show the 10th passenger on my list?

SELECT TOP 10 Passenger.Name, SUM(Manifest.Miles) AS [Total Miles], COUNT(*) AS [Number of Flights]
FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber = Manifest.PassengerNumber
GROUP BY Passenger.Name
HAVING SUM(Manifest.Miles) >= 20000
ORDER BY SUM(Manifest.Miles) DESC;

I tried to add a WHERE line and add the NOT IN function to that line, but kept getting an error message which I think had to do with the variable I’m tracking being a sum function (sum of total miles traveled). I could have added it in the wrong place or used the wrong wording, but I’m not sure. Every thing I tried came back with some sort of error message.