CODEWITHSUNDEEP
sqlmysqlpagination
chcne
chcne

Reputation: 91

Limit SQL query result in MySQL

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

ex:

etc

Upvotes: 9

Views: 46673

Answers (7)

Dora
Dora

Reputation: 292

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

Upvotes: 5

Saeed-rz
Saeed-rz

Reputation: 1443

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

Upvotes: 0

In silico
In silico

Reputation: 52129

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.

Upvotes: 11

indianwebdevil
indianwebdevil

Reputation: 5127

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

Upvotes: 1

miku
miku

Reputation: 188004

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

Upvotes: 2

kbrimington
kbrimington

Reputation: 25642

TSQL

SELECT TOP 10000 ...

PL/SQL

... WHERE ROWNUM < 10000 ...

Upvotes: 0

Jay
Jay

Reputation: 1356

select top x * from table in SQL Server

select * from table where ROWNUM < x in Oracle

select * from table limit x in MySQL

Upvotes: 3

Related Questions