Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

  • 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

like image 824
chcne Avatar asked Aug 03 '10 18:08

chcne


People also ask

How do I LIMIT MySQL results?

In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count. The value of both the parameters can be zero or positive integers.

How do I LIMIT SQL query results?

The SQL LIMIT clause constrains the number of rows returned by a SELECT statement. For Microsoft databases like SQL Server or MSAccess, you can use the SELECT TOP statement to limit your results, which is Microsoft's proprietary equivalent to the SELECT LIMIT statement.

How do you set a LIMIT in a query?

The limit keyword is used to limit the number of rows returned in a query result. “SELECT {fieldname(s) | *} FROM tableName(s)” is the SELECT statement containing the fields that we would like to return in our query. “[WHERE condition]” is optional but when supplied, can be used to specify a filter on the result set.

What does LIMIT 1 1 do in SQL?

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).


3 Answers

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

like image 70
Dora Avatar answered Oct 06 '22 04:10

Dora


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.

like image 23
In silico Avatar answered Oct 06 '22 02:10

In silico


select top x * from table in SQL Server

select * from table where ROWNUM < x in Oracle

select * from table limit x in MySQL
like image 39
Jay Avatar answered Oct 06 '22 04:10

Jay