Access DB: TOP clause and nested queries + PHP Paging

Question

I'm developing a PHP interface to show some results from two different databases: a MySQL one (.sql) and an Access one (.mdb). When I query for the results I let the user choose how many of them he wants to list, so that I can limit and page them through some PHP fun.
This is flawlessly working for the MySQL DB, where the LIMIT clause does everything in a neat way. The Access DB is a different talk: Access doesn't have a LIMIT clause, so that I've to use the TOP clause. Too bad that this one makes paging a pain in the back.
I've found a nice example in the accepted answer to this question, but I'm having an issue. First of all, let me show you a couple of snippet queries:

QUERY #1

SELECT TOP 5 *                 
FROM `Table_1` INNER JOIN `Table_2`
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID`
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp`
WHERE `Table_1`.`Some_ID` LIKE 'ID %' AND `Table_1`.`Timestamp` LIKE '2018/03/20 %';

QUERY #2

SELECT TOP 5 *                 
FROM `Table_1` INNER JOIN `Table_2`
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID`
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp`
WHERE `Table_1`.`Some_ID` LIKE 'ID %' AND `Table_1`.`Timestamp` LIKE '2018/03/20 %'
ORDER BY `Table_1`.`Some_ID`;

Query #1 shows me just the first 5 results, as asked.
Too bad that, when I add an ORDER BY clause, Query #2 gives me back ALL the results satisfying my conditions.

The same thing doesn't happen with a MySQL query like this:

QUERY #3

SELECT * 
FROM `Table_1` INNER JOIN `Table_2`
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID` 
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp` 
WHERE `Table_1`.`Some_ID` LIKE 'ID %' AND `Table_1`.`Timestamp` LIKE '2018/03/20 %'
ORDER BY `Table_1`.`Some_ID`
LIMIT 0, 5;

So, am I doing something wrong with my Access query?
To complete my question, this is how my final Access query should look like (I've slightly modified the one proposed in the aforementioned topic):

QUERY #4

SELECT * 
FROM `Table_1` INNER JOIN `Table_2` 
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID` 
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp` 
WHERE EXISTS (
    SELECT TOP 5 * FROM (
        SELECT TOP 10 * 
        FROM `Table_1` INNER JOIN `Table_2` 
        ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID` 
        AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp` 
        WHERE `Table_1`.`Some_ID` LIKE 'ID %' AND `Table_1`.`Timestamp` LIKE '2018/03/20 %') 
    ORDER BY `Table_1`.`Some_ID` DESC) 
ORDER BY `Table_1`.`Some_ID` ASC;

Well done for providing a clear and (fairly) concise question. — Strawberry, Mar 20 '18 at 08:04

Davide3i · Accepted Answer · 2018-03-21T13:20:17.600

At last I've managed to solve my issue. I'll post my query just in case someone else will have this same problem.

SELECT TOP 5 *
FROM `Table_1` INNER JOIN `Table_2`
ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID`
AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp`
WHERE `Table_1`.`Timestamp`
IN (
    SELECT TOP 5 `Table_1`.`Timestamp`
    FROM (
        SELECT TOP 10 *
        FROM `Table_1` INNER JOIN `Table_2`
        ON `Table_1`.`Some_ID` = `Table_2`.`Some_ID`
        AND `Table_1`.`Timestamp` = `Table_2`.`Timestamp`
        WHERE `Table_1`.`Some_ID` LIKE 'ID %'
        AND `Table_2`.`Timestamp` LIKE '2018/03/20 %'
        ORDER BY `Table_1`.`Timestamp` ASC)
    ORDER BY `Table_1`.`Timestamp` DESC)
ORDER BY `Table_1`.`Timestamp` ASC;

Just play around with "ASC" and "DESC" to obtain the results you are willing to get back.

I'm glad you were able to solve your issue - and a little surprised that no one else suggested an answer. But you are welcome to accept your own answer if you want to. — Strawberry, Mar 21 '18 at 13:19

Access DB: TOP clause and nested queries + PHP Paging

1 Answers1