LIMIT Clause MySQL Essential Training

LIMIT Clause MySQL Essential Training

MySQL

LIMIT Clause MySQL Essential Training




In this lesson we’re gonna learn about what is LIMIT clause and its various functionalities with the select statements. The LIMIT Clause MySQL Essential Training is mainly used to constrain the number of rows in a result set. Lets see how to do that with real time examples.
The LIMIT clause contains one or two arguments, the values of the both arguments must be zero or positive integers.
The following illustrates the LIMIT clause syntax with two arguments.

In the above syntax we’ve offset as one argument which is used to return the first row of the select statement. The offset of the first is 0, not 1. Its like the array we used in our PHP Essential Training series.
The second argument is the count which is used to return the number rows to be return from the select statements.
When we ignore the one argument and only uses the single argument then it is considered to be the maximum number or rows to be returned. Which means if we use only one argument with the LIMIT Clause MySQL Essential Training then it’ll be considered as the count argument.

The syntax we used above is almost equivalent to consider the offset as 0 and the LIMIT is accepts two arguments.

Using MySQL LIMIT to get the first N rows
We can use the LIMIT clause to return the N rows from the table as like given below.

Consider this scenario, if we online store and wants to show only five products from the vast database in the featured products then we can set limits to this situation.

LIMIT Clause MySQL Essential Training
LIMIT Clause MySQL Essential Training – Highest to Lowest
Like we used in our other clauses we can also use this LIMIT clause with SELECT, UPDATE and DELETE clauses. We can test this with order by query in the upcoming example.
For example consider our emp_details table in our sample database. Consider the management wants you to show the top three salaried employees in the org then we can use the following query to get the exact result.




LIMIT Clause MySQL Essential Training
Like the highest we can also get the lowest values from the database in LIMIT Clause MySQL Essential Training. Consider the following query.

LIMIT Clause MySQL Essential Training
LIMIT Clause MySQL Essential Training – Nth Highest Value
Another biggest task in the MySQL is find the largest or smallest from the nth values in a result. For example if we want to check who is the third highest salary person in the emp_details table. In such LIMIT Clause MySQL Essential Training situation we can’t use the MAX or MIN functions to get the exact result.
To do this we have to sort the result in descending order first. Then we can use the LIMIT Clause to get the nth highest salaried employee in the vast amount of data. Lets see how to do that.

Here we give the nth-1 at the place of the offset argument. As we mentioned earlier the offset initial value is 0 not 1.

LIMIT Clause MySQL Essential Training
The above shows all the rows from the table in a descending order. Now get the the nth highest salaried employee from this list. Now we LIMIT Clause to get the desired output.

LIMIT Clause MySQL Essential Training
In the above example we want the third high salaried person and so we use 2 as offset and limit as 1.
In this lesson, we have shown you how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement. We also introduced you to a technique to get the nth highest value. We hope this will teach you some basics about the LIMIT clause, we can explore lot more about it in upcoming lessons.




Leave a Reply