Limit SQL query result in MySQL
I would like to limit the amount of rows I fe开发者_运维问答tch 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
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.
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
select top x * from table in SQL Server
select * from table where ROWNUM < x in Oracle
select * from table limit x in MySQL
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
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
TSQL
SELECT TOP 10000
...
PL/SQL
... WHERE ROWNUM < 10000
...
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
精彩评论