mysql - Database: SQL Pagination?

Question

I'm using MySQL.

I want to limit the amount of rows fetched from the database. I have a SQL statement that returns 10,000 records which are all relevant to the search.

SELECT colA, colB, colC
FROM xyzTable
WHERE ...
ORDER BY colA

I know how to use the TOP statement to return the TOP x number of rows fetched but how do I fetch rows rows in the middle of the search result, for example, rows 50-100?

Answer 1

MySQL's limit clause makes this easy:

SELECT cols
FROM table
LIMIT offset, rowcount

In your case:

LIMIT 49, 50 -- skip rows 0-49, grab the next 50