Limit SQL query result in MySQL

Question

I would like to limit the amount of rows I fetch in MySQL. Can you show me how?

ex:

• 1st query I would like to retrieve only the first 10,000 records
• 2nd query I would like to retrieve only records from 10,000 - 20,000

etc

Answer 1

I think the following queries will give you the desired result

SELECT * FROM PERSON_TBL LIMIT 0, 10000

@ 1st query I would like to retrieve only the first 10,000 records

SELECT * FROM PERSON_TBL LIMIT 10000,10000

@ 2nd query I would like to retrieve only records from 10,000 - 20,000

Answer 2

The term you're looking for is "pagination." Unfortunately, this is done differently depending on the SQL engine.

For MS SQL Server, see this Stack Overflow question.

Since you mentioned MySQL, it's actually quite simple:

SELECT [columns] FROM [a table] LIMIT 10000
SELECT [columns] FROM [a table] LIMIT 10000 OFFSET 10000

The first statement fetches results 1-10,000, and the second statement fetches results 10,001-20,000.

Answer 3

select top x * from table in SQL Server

select * from table where ROWNUM < x in Oracle

select * from table limit x in MySQL