CODEWITHSUNDEEP
sqllimit
Mike Jones
Mike Jones

Reputation: 11

MS Access limit syntax

I have a very basic Access DB table called SCHEDULE. When I make the following SQL Query entry, things work perfectly.

SELECT *
FROM schedule
ORDER BY start;

Now what I want is to skip the first 3 records and view the remaining 10 like so:

SELECT *
FROM schedule
ORDER BY start
LIMIT 3,7;

But I receive a "SYNTAX ERROR (MISSING OPERATOR) IN QUERY EXPRESSION 'START LIMIT 3'

So as a test I tried the following:

SELECT *
FROM schedule
ORDER BY start
LIMIT 0,3;

Same error.

What is the correct syntax?

Upvotes: 1

Views: 2937

Answers (2)

Zanon
Zanon

Reputation: 30710

There is no LIMIT operator in MS Access, that's why you are getting the syntax error, but you can get this result using TOP and a subquery, like the following:

SELECT *
FROM schedule
WHERE schedule.ID IN 
      (
        SELECT TOP 7 SUB.ID
        FROM [
               SELECT TOP 10 schedule.start, schedule.ID
               FROM schedule
               ORDER BY schedule.start, schedule.ID
             ]. AS SUB
        ORDER BY SUB.start DESC, SUB.ID DESC
      )
ORDER BY schedule.start, schedule.ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this with subqueries is MS Access:

select top 7 *
from (select top 10 *
      from schedule
      order by start
     ) as s
order by start desc;

If you want the final results in ascending order rather than descending order, use this as a subquery and add order by start.

EDIT:

If you just want to avoid the first three:

select *
from schedule
where start not in (select top 3 start from schedule order by start);

Upvotes: 1

Related Questions