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 the OFFSET option, we can use the clever combination of TOP , subquery, and view to get the same result.

Here is an example of getting 10 rows, starting at offset 20 in the Identity table in ORDER BY Name and Identifier ..

 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 request the top 30, cancel the order, request the top 10, and then select the rows from the table that match, sorting in the direct order again. This should be quite efficient, assuming Id is a PRIMARY KEY , and there is an index in the Name . Perhaps, for best performance, you need a specific coverage index Name , Id (and not just Name alone), but I implicitly cover the indexes of PRIMARY KEY .

Answer 2

Another way. Let's say you want 1000 to 1999 records in a table called table1 (of course, if you have 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 it 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

Answer 3

Best query:

 SELECT Users.* FROM Users WHERE Users.id In ( SELECT TOP X A.id FROM [ SELECT TOP Y Users.* FROM Users ORDER BY Users.reg_date DESC ]. AS A ORDER BY A.reg_date ASC ) ORDER BY Users.reg_date DESC 

Where

 if((totalrows - offset) < limit) then X = (totalrows - offset) else X = limit 

and

 Y = limit + offset 

For example, if total_rows = 12, and we set the limit to 10 (show 10 users on the page), and offset is calculated as p * limit - (limit) , where p is the number of the current page, so on the first page ( p = 1 ) we get : X = 12 and Y = 10 , on the second X = 2 and Y = 20 . The list of users is ordered by registration date (descending).

Answer 4

Simple and quick solution.

myTable {ID *, Field2, Filed3 ...}

---

• Suppose your SortOrder contains the main KEY only

   SELECT TOP PageItemsCount tb01.* FROM myTable AS tb01 LEFT JOIN ( SELECT TOP OffsetValue ID FROM myTable ORDER BY ID ASC ) AS tb02 ON tb01.ID = tb02.ID WHERE ISNULL(tb02.ID) ORDER BY tb01.ID ASC 

---

2. SortOrder based on other fields with duplicate values, in this case you must include your primary key in SortOrder as the last.

For example, myTable

 +-------+--------+--------+ | ID | Field2 | Filed3 | +-------+--------+--------+ | 1 | a1 | b | | 2 | a | b2 | | 3 | a1 | b2 | | 4 | a1 | b | +-------+--------+--------+ SELECT TOP 2 * From myTable ORDER BY FIELD2; +-------+--------+--------+ | ID | Field2 | Filed3 | +-------+--------+--------+ | 2 | a | b2 | | 4 | a1 | b | | 3 | a1 | b2 | | 1 | a1 | b | +-------+--------+--------+ SELECT TOP 2 * From myTable ORDER BY FIELD2, FIELD3; +-------+--------+--------+ | ID | Field2 | Filed3 | +-------+--------+--------+ | 2 | a | b2 | | 4 | a1 | b | | 1 | a1 | b | +-------+--------+--------+ 

But if we add the identifier to the sort order [ LIST OF LAST FIELDS ]

 SELECT TOP 2 * From myTable ORDER BY FIELD2, ID; +-------+--------+--------+ | ID | Field2 | Filed3 | +-------+--------+--------+ | 2 | a | b2 | | 1 | a1 | b | +-------+--------+--------+ 

Final request

  SELECT TOP PageItemsCount tb01.* FROM myTable AS tb01 LEFT JOIN ( SELECT TOP OffsetValue ID FROM myTable ORDER BY Field2 ASC, ID ) AS tb02 ON tb01.ID = tb02.ID WHERE ISNULL(tb02.ID) ORDER BY tb01.Field2 ASC, tb01.ID 

Answer 5

No, JET SQL has no direct equivalent. As a workaround, you can add a WHERE that selects an ordered / identifier column between two values.

If possible, you can also use end-to-end queries against your existing MySQL / other database.

While TOP in MS-Access can limit the returned records, it does not accept two parameters, as with the MySQL LIMIT keyword (see this question ).

Answer 6

You can definitely get the equivalent of "Limit" using the top keyword. Cm:
Keyword Access Limit Database