MySQL Limit Results Returned With LIMIT

You eventually hit the stage where data volume greatly increases when we start to deal with DBMS like MySQL. It is difficult for us to manage and use. MySQL has built-in capabilities that make it easy to handle. In MySQL, the LIMIT clause is being used to cut down the number of rows throughout the result set using the SELECT expression. We will discover how to use the MySQL LIMIT clause in this guide to restrict the number of rows that a query returns.

LIMIT Syntax:

>> SELECT Expression FROM table LIMIT Offset, Rows;

Here is the explanation of the LIMIT query:

Expression: Can be a column name or steric ‘*’.
Offset: The offset determines the offset to be returned from the first row. If you use the 0 as offset, it will return the row 1 and vice versa.
Rows: The total rows to be returned.

Open the command-line shell of MySQL and type the password. Press Enter to continue.

Assume you have a table called ‘teacher’ in your MySQL database, as shown below. If you want to fetch all the records or rows of this table without any limit define, you will do it using the simple SELECT query as follows:

>> SELECT * FROM data.teacher;

Example 01: LIMIT with Row Number Only:

If a user wants to fetch some records while limiting the number of rows, he/she can do it by using the simple LIMIT clause in the SELECT statement. Let’s try an example while utilizing the above table. Suppose you want to display only 6 rows from the above table while displaying the rows following the descending order of a column TeachName. Try the following query:

>> SELECT * FROM data.teacher ORDER BY TeachName DESC LIMIT 6;

Example 02: LIMIT With OFFSET and Row Number:

Let’s try the same table to define Offset alongside the row number. Suppose you need to fetch only 6 records from the table while the record returned must be starting from the 6^th row of a table. Try the succeeding query:

>> SELECT * FROM data.teacher DESC LIMIT 6, 6;

Example 03: LIMIT with WHERE Clause:

Assume the table ‘same’ is located in the MySQL database. Fetch the whole table while sorting it in ascending order of column ‘id’ using the SELECT command along with ORDER BY as follows:

>> SELECT * FROM data.same ORDER BY id ASC;

Trying the stated query, we will have random three records of the age between 12 and 34 from any random location of the table. While in the above table, we have more than 6 records of age group between 12 and 34.

>> SELECT * FROM data.same WHERE age > ‘12’ AND age < ‘34’ LIMIT 3;

When we use the ORDER BY clause in the query without stating the order type, it will automatically fetch the record in ascending order as below.

>> SELECT * FROM data.same WHERE age > ‘12’ AND age < ‘34’ ORDER BY age LIMIT 3;

To fetch a limited record in another sort order, you have to define the sort order. As we are fetching 8-row data using the descending order of column ‘age’.

>> SELECT * FROM data.same WHERE age > ‘12’ AND age < ‘34’ ORDER BY age DESC LIMIT 8;

Let’s fetch only 2 rows where the pet name is ‘dog’. On execution, we have only 1 result because the ‘=’ sign searched for the exact pattern, and we have only 1 record of its pattern.

>> SELECT * FROM data.same WHERE pet = ‘dog’ ORDER BY id LIMIT 2;

Example 04: LIMIT with LIKE Clause and It’s Wildcards:

We searched for the pattern ‘dog’ and got only 1 result. While we have more than 1 record of ‘dog’ in the table. Now we will fetch those records using the LIKE clause along with the LIMIT clause. To display the only 5 records of the table where the pet name starts from ‘d’, try the below query. As we have only 2 records for pattern ‘dog’, that’s why we have got only 2.

>> SELECT * FROM data.same WHERE pet LIKE ‘d%’ ORDER BY id LIMIT 5;

Let’s retrieve only 10 records from the table, where the pet must have ‘r’ at any middle location of its name. As we have horse, parrot, and rabbit in the pets having ‘r’ in their name, that’s why we have got only 4 records from this query.

>> SELECT * FROM data.same WHERE pet LIKE ‘%r%’ ORDER BY id LIMIT 10;

To get 6 records of the table, where the pet name must have ‘t’ at the end, run the stated query in the SQL command-line shell. Here we have 4 records from this query.

>> SELECT * FROM data.same WHERE pet LIKE ‘%t’ ORDER BY id LIMIT 6;

To get 8 records of the table, where a person’s job must have ‘er’ at the end, run the below query in the shell. Here we have got 6 records from this query.

>> SELECT * FROM data.same WHERE job LIKE ‘%er’ ORDER BY age ASC LIMIT 8;

Let’s change the WHERE clause along with the LIKE statement in the SELECT query. Suppose you want to fetch a 6-row record from the table ‘same’. You have tried a condition to fetch the only records where the ‘fname’ must have ‘a’ at the last of its value, and ‘lname’ must have ‘a’ at any middle location of its value. On the other hand, the data must be sorted following the column ‘age’ descending order. To get these 6 records, run the below-stated query in the SQL command-line shell. We have got 5 records for this condition, and the result is shown below.

>> SELECT * FROM data.same WHERE fname LIKE ‘%a’ AND lname LIKE ‘%a%’ ORDER BY age DESC LIMIT 5;

Conclusion:

I hope you are fully prepared about the LIMT topic after trying almost all the examples for the LIMIT clause along with its members, e.g., Offset and row number.