LIMIT in MySQL SELECT Statement
MySQL SELECT statement is very much like a regular SELECT statement but it has one great clause which i would like to see in all modren RDBMS, the LIMIT clause.
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement in MySQL. The LIMIT clause takes one or two numeric arguments, both must be non-negative integer constants.
The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 NOT 1. This clause is very helpful for front-end application developers when writing an application which require from row to row functionality. In web pages you will notice paging, if you are browsing articles on this site then articles page display 10 articles at a time and provides you option to go on page two, three, four, etc. If you are using SQL Server it is little extra coding and not the most effecient way but MySQL LIMIT clause makes it a peice of cake.
MySQL Limit clause examples
SELECT * FROM TableName LIMIT 5;
The query above will retrieve first 5 rows only starting from first row.
SELECT * FROM TableName LIMIT 5,10;
This second query will retrieve 10 rows starting from row 6, the first parameter provides the offset (the starting row) and the second parameter limits the number of rows returned. As an example If you want to view all the rows starting at row 25 then you can use a very high number as second parameter. See example of the query below.
SELECT * FROM TableName LIMIT 24, 1000000000 ;
If LIMIT occurs within a subquery and also in the outer query, the outermost LIMIT takes precedence. You can see the advantages of LIMIT clause provided by MySQL team.
In SQL Server 2005 you can use SQL Server 2005 ROW_NUMBER builtin function for paging.