MariaDB Tutorial

MariaDB Tutorial MariaDB Drop Database MariaDB Alter Database MariaDB Create a Database MariaDB Aggregate Functions MariaDB Group By Clause MariaDB Drop And Truncate Table MariaDB Like Clause MariaDB Order By Clause MariaDB Intersect Operator MariaDB Having Clause MariaDB Union MariaDB String Functions MariaDB Numieric Funcitons Difference Between MariaDB & MySQL

MariaDB - LIKE Clause
2022-04-22 09:04:19

MariaDB - LIKE Clause

We use the LIMIT clause to limit or control the number of rows returned by the SELECT statement. We have to specify the m integer it will return initial m rows.

You can also use the ORDER BY clause to return the initially ordered rows.

There are two syntax we can use:

LIMIT rowCount OFFSET offset;
OR
SELECT expression FROM table_name [WHERE_condition] [ORDER BY exp [ASC|DESC]] LIMIT rowCount;
  • In the first syntax, if we specify an offset as x with the limit as y, the first x number of rows will be disregarded, and the following y number of rows will be returned by MariaDB.
  • The Expression after SELECT is the column list we want to retrieve
  • WHERE condition can be mentioned if we wish to retrieve particular rows.
  • ORDER BY clause can be used to order the rows in a particular order.
  • And finally, the LIMIT rowCount is the number of rows we wish to retrieve.

Let’s see an example:

We will be using a table named states with two columns ‘code’ and ‘name’ where in code in integer column and name is character column.

MariaDB - LIKE Clause MariaDB - LIKE Clause

Initially, we will see how order by clause works and list the names in ascending order

Next, we will use the LIMIT clause.

MariaDB [student]> select name from states order by name Limit 5;

Output:

MariaDB - LIKE Clause

Above, we have use order by clause along with LIMIT clause; therefore, the query returns the first five alphabetically ordered rows.

Next, we’ll be using OFFSET.

MariaDB [student]> select name from states order by name Limit 10 offset 6;

Output:

MariaDB - LIKE Clause

We have used the OFFSET keyword; We are skipping the first six rows and selecting the next ten states.

We can also do the same thing without using the OFFSET keyword; the result would be the same.

MariaDB [student]> select name from states order by name Limit 6,10;
MariaDB - LIKE Clause

Here we have skipped the OFFSET keyword, so the first integer ‘6’ identifies as OFFSET and ‘10’ as a LIMIT integer.

RETRIEVE TOP-N ROWS

MariaDB [student]> select code, name from states order by code desc limit 15;
MariaDB - LIKE Clause

We can use the LIKE clause can also be used to analyze the data. We have arranged the states based on code in descending order, and we have retrieved the top 15 rows.

TO RETRIEVE Nth ROW

We can also use the LIMIT clause to retrieve the nth row from the table. We have to use both OFFSET and LIMIT together.

For example:

In the following query, we are using order by clause along with the LIMIT clause. First, the rows will be sorted by ORDER BY clause, here we have used DESC keyword so that rows will be sorted in descending order.

MariaDB [student]> select code, name from states order by code desc limit 3,1;
+------+---------+
| code | name    |
+------+---------+
|   25 | Mizoram |
+------+---------+

With the limit clause, we have used offset as ‘3’, so the offset first three states with the largest code will be skipped, and the rowCount is specified as one, so the 4th row is selected, i.e., 4th largest code of all states.