MS Access LIMIT X, Y

Question

Is it possible to emulate the following MySQL query:

SELECT * FROM `tbl` ORDER BY `date` DESC LIMIT X, 10

(X is a parameter)

in MS Access?

Answer 1

While the Access/JET TOP keyword does not directly provide an OFFSET capability, we can use a clever combination of TOP, a subquery, and a "derived table" to obtain the same result.

Here is an example for getting the 10 rows starting from offset 20 in a Person table in ORDER BY Name and Id...

SELECT Person.*
FROM Person
WHERE Person.Id In 
      (
        SELECT TOP 10 A.Id
        FROM [
               SELECT TOP 30 Person.Name, Person.Id
               FROM Person
               ORDER BY Person.Name, Person.Id
             ]. AS A
        ORDER BY A.Name DESC, A.Id DESC
      )
ORDER BY Person.Name, Person.Id;

Essentially, we query the top 30, reverse the order, query the top 10, and then select the rows from the table that match, sorting in forward order again. This should be fairly efficient, assuming the Id is the PRIMARY KEY, and there is an index on Name. It might be that a specific covering index on Name, Id (rather than one on just Name) would be needed for best performance, but I think that indexes implicitly cover the PRIMARY KEY.

Answer 2

Another way - Let say you want from 1000 to 1999 records in a table called table1 (of course if you have that many records) you can do something like this.

MSSQL

SELECT *
    FROM table1 LIMIT 1000, 1999;

MS Access

SELECT TOP 1000 * 
FROM table1 
Where ID NOT IN (SELECT TOP 999 table1.ID FROM table1);

To break this down

SELECT TOP NumA * 
FROM table1 
Where ID NOT IN (SELECT TOP NumB table1.ID FROM table1);

UpperLimit = 1999

LowerLimit = 1000

NumA = UpperLimit - LowerLimit + 1

ex. 1000 = 1999 - 1000 + 1

NumB = LowerLimit -1

ex. 999 = 1000 - 1