Syntax error Is MySQL LIMIT applied before or after ORDER BY?

Is MySQL LIMIT applied before or after ORDER BY?

The MySQL LIMIT is applied after ORDER BY. Let us check the limit condition. Firstly, we will create a table −

mysql> create table LimitAfterOrderBy
   −> (
   −> Id int,
   −> Name varchar(100)
   −> );
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into LimitAfterOrderBy values(101,'John');
Query OK, 1 row affected (0.18 sec)

mysql> insert into LimitAfterOrderBy values(102,'Carol');
Query OK, 1 row affected (0.15 sec)

mysql> insert into LimitAfterOrderBy values(103,'Bob');
Query OK, 1 row affected (0.21 sec)

mysql> insert into LimitAfterOrderBy values(104,'Sam');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from LimitAfterOrderBy;

The following is the output −

+------+-------+
| Id   | Name  |
+------+-------+
| 101  | John  |
| 102  | Carol |
| 103  | Bob   |
| 104  | Sam   |
+------+-------+
4 rows in set (0.00 sec)

Here is the query when LIMIT comes after ORDER BY −

mysql> select *from LimitAfterOrderBy order by Name limit 4;

The following is the output −

+------+-------+
| Id   | Name  |
+------+-------+
|  103 | Bob   |
|  102 | Carol |
|  101 | John  |
|  104 | Sam   |
+------+-------+
4 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:24+05:30

143 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements