SQL Access Equivalent MySQL Limit X, Y

In MySQL for paging a query we use

  • SELECT * FROM {table} LIMIT X, Y

In Access there is only

  • SELECT TOP Y * FROM {table}

to select first Y records of a query

We can use this trick:
First we have to know the num_rows of the query

  • NUM_ROWS => “SELECT COUNT(`{$primaryKey}`) FROM {$table} {$where}”

Then we can execute the query we want is equivalent to SELECT * FROM {table} LIMIT X, Y in MySQL

  • SELECT TOP Y * FROM (
    SELECT TOP (NUM_ROWS – X) * FROM {$table} {$where} {$order_inv}
    ) {$order}

Legend:
{$table} is the table name
{$where} is the where condition
{$order} is the order desired
{$order_inv} is the opposite order desired

This entry was posted in Access, PHP, SQL and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s