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?

Actinoid

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.

Higinbotham

However considering limit is 10 and there is 11 rows, the offset 10 will actually return all the rows (10) except the first. So it is pretty similar to queue mechanism - you just pop out the first element, and the last one is being pushed. But the idea is to display only one (last) row, when the offset is 10, which your query doesn't do. – Actinoid

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

Fancyfree

Answer 3

A better query would be:

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 per page), and the offset is calculated as p * limit - (limit) where p is the number of the current page, hence in the first page (p = 1) we will get: X = 12 and Y = 10, on the second X = 2 and Y = 20. The list of users is ordered by registration date (descending).

Actinoid

When using TOP for pagination, it's best to include the primary key in the sort as a tie breaker and to ensure a predictable ordering. – Higinbotham

Looks good, except that I think X should be the maximum of the limit or (total_count - offset). Otherwise, unless you are querying the final page, you'll end up with more result rows than intended. – Higinbotham

@Steve Jorgensen regarding the primary key - you are completely right. I just wanted to simplify for myself the understanding of this "trick". What about the X remark - I didn't quite understand you here. It is fine because if we take the example I've posted, the query of the last page (page = 2) would be SELECT TOP 2 above SELECT TOP 20, which will actually give you only two results as expected. It will never repeat the rows from previous pages as it is calculated by total_rows - offset. So everything seems fine for me. – Actinoid

The problem with your X calculation is what happens when you're not on the last page. If you had 35 total rows, and you want offset=11, limit=10, then using your rule for X and Y, X would be 24, and Y would be 21. As a result, you would get back 21 rows. X should only be total_count - offset, when that number is < limit (on the last page). Otherwise, X should simply be equal to the limit. – Higinbotham

@Steve Jorgensen - oh, sorry, you're right. I've chosen your answer as best because you gave a good explanation. I think it will be good If you add this point I mentioned in my answer so others could see it. Thanks again. – Actinoid

Answer 4

Simple and fastest solution.

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

---

1. Assume your SortOrder contain primary 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 duplicated values, in this case you must include your primary key in SortOrder as last one.

For exemple, 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 ID to sort order [AS LAST IN FIELDS LIST]

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

Ambala

Answer 5

No, JET SQL does not have a direct equivalent. As a workaround, you could add a WHERE clause that selects an ordered/id column between two values.

If possible, you can also use pass-through queries to an existing MySQL/other database.

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

Am

Thanks. Another reason to hate MS Access. – Actinoid

Answer 6

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

Unamuno

The Top keyword does not have two parameters as with the MySQL Limit keyword – Am

I do know about the TOP keyword, and I use it wherever it is possible. However I can't emulate the OFFSET. That's the point. – Actinoid

@CameronS and @msgdie It is well known fact that MySQL's LIMIT can be obtained by combination of TOP, for example look at Steve Jorgensen's answer. To @Zeki, your answer is not full, it looks more like a comment, and the link, which you provided, doesn't realy concerns the question about LIMIT. Please, explain in more details how to get LIMIT's analog, and I will vote you up. – Clothier