getting the i-th to j-th rows within an ordered Mysql query

so suppose I have this query:

SELECT * FROM t WHERE j = k开发者_Go百科 ORDER BY l

is there a way to have the query only return, for example the 20th to 40th records according to the ORDER BY statement rather than returning all the rows ordered by the ORDER BY statement?

SELECT * FROM t WHERE j = k ORDER BY l LIMIT 20, 20

Limit by 20 (the second one), starting from offset 20, the first.

Did you search in the documentation at all?

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.

LIMIT + OFFSET

SELECT * FROM t WHERE j = k ORDER BY l LIMIT 19,21

Note:

• the offset is zero based so 19 = start at row 20
• and there are 21 rows between 20 and 40 inclusive

SELECT * FROM t WHERE j = k ORDER BY l LIMIT 20 OFFSET 20;

limit says you only need 20 rows, and offset says you don't need the first 20 rows.

Keep in mind that when the offset value is very large, your query will be very slow, since the sql server will need to scan the first <offset> rows to return <offset>+<limit> rows to you.

http://dev.mysql.com/doc/refman/5.0/en/select.html

sample: SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

so

SELECT * FROM t WHERE j = k ORDER BY l LIMIT 20,20; #retrieve rows 20-40

There's solution: SELECT * FROM t WHERE j = k ORDER BY l limit 21, 20; (21 is number of rows and 20 is starting row, so you will retrieve rows from 20th to 40th if they exists)