MS Access LIMIT X, Y

by

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.

More Related Contents:

  1. Combine values from related rows into a single concatenated string value
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. How to get random record from MS Access database
  5. “General error Unable to open registry key Temporary (volatile) …” from Access ODBC
  6. create form to add records in multiple tables
  7. Operation must use an updatable query. (Error 3073) Microsoft Access
  8. How do I force VBA/Access to require variables to be defined?
  9. Hand Install of 64-bit MS Access ODBC drivers when 32-bit Office is present
  10. Running Microsoft Access as a Scheduled Task
  11. How to diagnose MS access crashes
  12. “Operation must use an updateable query” error in MS Access
  13. How to restart counting from 1 after erasing table in MS Access?
  14. Equivalent cURL in VBA?
  15. VBScript & Access MDB – 800A0E7A – “Provider cannot be found. It may not be properly installed”
  16. MS Access trigger?
  17. Insert SQL command with Datetime in MS-Access
  18. Is MS Access (JET) suitable for multiuser access?
  19. VBA procedure to import csv file into access
  20. How to select top 10 in Access query?
  21. ConcatRelated() in Access, using two keys
  22. Concatenating multiple rows into single line in MS Access [duplicate]
  23. Transform multiple rows into columns with unique key
  24. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  25. Access VBA: Find max number in column and add 1
  26. Access 2007 – INSERT and instant SELECT doesn’t retrieve the inserted data
  27. Create sequential ID value based on the year that a record is added
  28. How to use cross join in access?
  29. Get Name of Current VBA Function
  30. How to extract the schema of an Access (.mdb) database?

Leave a Comment