0

How would I find the 11th entry in an Access SQL database table? I have to use the TOP function, and my query currently looks like this: 

SELECT TOP 11 Passenger.Name, SUM(Manifest.Ticketprice) AS [Total Amount Spent on Tickets]
FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber=Manifest.PassengerNumber
GROUP BY Passenger.Name
ORDER BY SUM(Manifest.Ticketprice) DESC;
E1234
  • 11
  • 1

2 Answers2

2

You can use a subquery:

SELECT TOP 1 T.Name, T.[Total Amount Spent on Tickets]
FROM

(SELECT TOP 11 Passenger.Name, SUM(Manifest.Ticketprice) AS [Total Amount Spent on Tickets]
FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber=Manifest.PassengerNumber
GROUP BY Passenger.Name
ORDER BY SUM(Manifest.Ticketprice) DESC) As T

ORDER BY T.[Total Amount Spent on Tickets] ASC
Gustav
  • 53,498
  • 7
  • 29
  • 55
-1
SELECT TOP 11 Passenger.Name, SUM(Manifest.Ticketprice) AS [Total Amount Spent on Tickets]
FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber=Manifest.PassengerNumber
GROUP BY Passenger.Name
ORDER BY SUM(Manifest.Ticketprice) DESC LIMIT 1;
Jorge Teruya
  • 1
  • 2
    There is no "LIMIT" keyword in access. I believe the answer is to use a nested "TOP" query: SELECT TOP 1 * FROM (SELECT TOP 11 Passenger.Name, SUM(Manifest.Ticketprice) AS [Total Amount Spent on Tickets] FROM Passenger INNER JOIN Manifest ON Passenger.PassengerNumber=Manifest.PassengerNumber GROUP BY Passenger.Name ORDER BY SUM(Manifest.Ticketprice) DESC); – Ricardo Olivo Poletti Jul 03 '17 at 17:47