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

by doing something like:

SELECT colA, colB, colC FROM xyzTable WHERE ... ORDER BY colA LIMIT 30 , 40

30 being the number you want to start on, 40 being now many items you want to see.

Then you can increment these values as you please.

Answer 2

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

Answer 3

Use LIMIT:

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT 50, 50

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments

Answer 4

SELECT colA, colB, colC
FROM xyzTable
WHERE ...
ORDER BY colA
LIMIT 10, 5

The LIMIT 10, 5 defines the range of results (this would return results 11,12,13,14,15).

Answer 5

SELECT colA, colB, colC
FROM xyzTable
WHERE ...
ORDER BY colA LIMIT 49,50;

LIMIT row-offset,number-of-rows The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return...