Search code examples
sqlms-accessms-access-2016

Problem with returning nth row from MS Access table

I'm trying to select the nth row of a table in MS Access (Office 365). I've seen both of the following solutions:

https://stackoverflow.com/a/45031166/1907765

https://stackoverflow.com/a/44891583/1907765

And neither of them have worked for me. When I wrote a query based on these answers, the query returned the last n rows in a table, and then selected the first result in that. E.g. if I was looking to select the 3rd row, it would select the 3rd-to-last row. Here's my query:

SELECT TOP 1 Sense.SenseID
FROM
(
SELECT TOP 3 Sense.SenseID
FROM Sense
ORDER BY Sense.SenseID DESC
)
ORDER BY Sense.SenseID ASC

Any idea what I'm doing wrong, and how to generate the correct result?

Solution

  • The order bys should be reversed:

    SELECT TOP 1 
    s.SenseID
FROM
 (SELECT TOP 3 s.SenseID
  FROM Sense AS
  ORDER BY s.SenseID Asc) AS s
ORDER BY 
    s.SenseID Desc;