CODEWITHSUNDEEP
sqlms-accessms-access-2016
Lou
Lou

Reputation: 2509

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?

Upvotes: 2

Views: 211

Answers (2)

Gustav
Gustav

Reputation: 55816

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;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You need a table alias so the syntax is correct. Try this:

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

This assumes that Sense.SenseID is unique -- but that seems like a reasonable assumption.

Upvotes: 1

Related Questions