2

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?

Lou
  • 2,200
  • 2
  • 33
  • 66

2 Answers2

3

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;
Gustav
  • 53,498
  • 7
  • 29
  • 55
1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the answer - I've tried this and it still returns the third-to-last result, not the third. Is there anything else needed to make this work? – Lou Dec 12 '19 at 12:13
  • 1
    @Lou . . . Just swap the `DESC` and `ASC` in the queries. It is not clear what *you* mean by "third" and third highest and third lowest values are both valid interpretations. This version is the third highest. – Gordon Linoff Dec 12 '19 at 13:35