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

TSQL

SELECT TOP 10000 ...

PL/SQL

... WHERE ROWNUM < 10000 ...

Answer 2

in mysql you do as follows

SELECT * FROM PERSON_TBL LIMIT 0, 1000 

SELECT * FROM PERSON_TBL LIMIT 1000, 1000 

Query 1 will fetch first 1000 records,

Query 2 will fetch next 1000 records

Syntax for limits clause

LIMITS OFFSET, ROWCOUNT

Where ROWCOUNT give number of row to fetch

OFFSET gives from which row to fetch more info here

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

Answer 4

MySQL and PostgreSQL support OFFSET that is usually used with a LIMIT clause.

SELECT column FROM table
LIMIT 10000

SELECT column FROM table
LIMIT 10000 OFFSET 10000

Answer 5

in MySQL :

SELECT * FROM `your_table` LIMIT 0, 10000 

This will display the first 10000 results from the database.

 SELECT * FROM `your_table` LIMIT 10000, 20000 

This will show records 10001, 10002, ... ,20000

Answer 6

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