Home / SQL Statements / Select Statement / Question No: 120

I want to fetch the desired number of rows from the database. How can I set such limit in MySQL?

MySQL provides LIMIT clause to use in the query to fetch desired number of rows from the database. So, you can use LIMIT clause in your query like this:

SELECT buyer_id, buyer_name FROM buyer_table LIMIT 10;

The above query will show first 10 rows from the buyer_table table. However, if you want to fetch last 10 rows of the table, you can also use LIMIT clause for the purpose like below. In the following LIMIT clause example I assume that buyer_id is an auto_increment field:

SELECT buyer_id, buyer_name FROM buyer_table LIMIT 10 ORDER BY buyer_id DESC;

If you want to fetch the rows between 10 and 20 you can again use the LIMIT clause as follow:

SELECT buyer_id, buyer_name FROM buyer_table LIMIT 9, 10;