Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to use the NOT IN function to find #10 in list of Top 10?

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.

Tags:

2

Answers


  1. 0

    The trusted method in Access SQL goes like this:

    Select Top 1 * From Table1
Where Id Not In
    (Select Top 10 Id From Table1 Order By Miles Desc)
Order By Miles Desc
    Login or Signup to reply.
  2. 0

    MySQL

    SELECT Passenger.Name, SUM(Manifest.Miles) AS TotalMiles, COUNT(*) AS NumberOfFlights
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
LIMIT 1 OFFSET 9   ;

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search