Selecting specific rows in MySQL

Selecting Top N

Following select query returns top ten rown from my table. As there is no ORDER BY clause, it returns arbitery rows.
SELECT * FROM mytable LIMIT 10;

To return rows with specific sorting use ORDER BY as follows:

SELECT * FROM myTable ORDER BY myId LIMIT 10;


Selecting Top N to M

MySQL provide special keyword called OFFSET. This works like 'to' as in 10 to 20. So to return top 10 to 20 rows SELECT query would be
SELECT * FROM myTable ORDER BY myId LIMIT 10 OFFSET 20;
or simply SELECT * FROM myTable ORDER BY myId LIMIT 10, 20;

Passing the LIMIT dynamically
If you want to pass the LIMIT value as a parameter to a stored procedure, the it has to be written little bit differently. Here how :
CREATE PROCEDURE USP_GETMYTABLEENTRIES( startAt INT(2), numberOfRecords INT(2))
BEGIN
SET @_start = startAt, @records = numberOfRecords;PREPARE myStatement FROM 'SELECT * FROM myTable ORDER BY myId ASC LIMIT ?, ?';EXECUTE myStatement USING @_start, @records;DEALLOCATE PREPARE myStatement;
END;