Access equivalent to LIMIT

[epimp702]

What's Jet/Access SQL for
SELECT * FROM MyTable LIMIT 5,10

I'm aware of TOP but I want to skip the first x posts.

 

[Brendan Reynolds]

I'm not aware of any direct equivalent, but something like the following
sub-query should do it ...

SELECT TOP 10 Orders.*
FROM Orders
WHERE (((Orders.OrderID) Not In (SELECT Top 5 Orders.OrderID FROM Orders
ORDER BY OrderID)))
ORDER BY Orders.OrderID;

 

[Graham R Seach]

In relational databases, the "first" of anything is meaningless. There is no
"first", or "last", or anything in between; there is only a sort order.

What are you really trying to do?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[epimp702]

In relational databases, the "first" of anything is meaningless. There is no
"first", or "last", or anything in between; there is only a sort order.

What are you really trying to do?

I'm aware that I should add ORDER BY field in order for it to make real
sense.

What I want is a search results page where results 10 to 20 are shown.

 

[Graham R Seach]

I wasn't suggesting you use the ORDER BY clause. What I was attempting to
explain was that in relational databases, there is no "first", "second", or
"third" record, because they can be stored on disk in almost any order.

As for returning a list where the contents of a field are between 10 and 20:
SELECT * FROM tblMyTable WHERE fieldA BETWEEN 10 AND 20

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[epimp702]

As for returning a list where the contents of a field are between 10 and 20:
SELECT * FROM tblMyTable WHERE fieldA BETWEEN 10 AND 20

Please excuse my foolishness but if there are only 7 entries with
values in that range then I won't have 10 entries shown. Google, MSN,
Yahoo don't show a random number of results per result page and neither
do I want to.

Is there really no MS Access/Jet alternative to MySQL's LIMIT?

 

[Brendan Reynolds]

Does the sub-query I posted earlier not achieve the result you want?

 

[Graham R Seach]

No, Jet doesn't have the equivalent of LIMIT. Brendan's suggestion about
using TOP n is the only way to do it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------