Limit statement in mysql

Hi Naresh,

The LIMIT clause is used to specify the number of records to return from your select query. The LIMIT clause is useful when you are delaing with large number of records. You can achieve pagination using Offset and Limit.

The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.

The following illustrates the LIMIT clause syntax with two arguments:

SELECT 
    select_list
FROM
    table_name
LIMIT [offset,] row_count;

The offset specifies the offset of the first row to return whereas row_count specifies the maximum number of rows to return.

LIMIT return top records in with Ascending order, If query has explicitly order by clause with LIMIT then order clause first executed then LIMIT will apply.

LIMIT clause is useful for pagination to display data on the screen. 

MySQL Limit query is used to restrict the number of rows returns from the result set, rather than fetching the whole set in the MySQL database. The Limit clause works with the SELECT statement for returning the specified number of rows only. This query accepts only one or two arguments, and their values should be zero or any positive integer.

It is essential in such a case when the table contains thousands of rows, or you want to return only the recently inserted data. In other words, if you are not interested in getting all the rows returned from the query, use the MySQL Limit clause with the SELECT statement. It improves the performance of the query and even stops having crashed the system when the table contains a large number of data.

To get only the specified rows from the table, MySQL uses the LIMIT clause, whereas SQL uses the TOP clause, and Oracle uses the ROWNUM clause with the SELECT statement.