MySQL - ORDER BY and LIMIT
MySQL
In this tutorial we will learn to order and limit the results in MySQL.
ORDER BY
We use ORDER BY to sort the result in ascending order and descending order based on some column or columns.
By default, ORDER BY will sort the result in ascending order.
To sort the result in descending order we using the DESC keyword.
Sort in ascending order using single column
In the following example we are sorting the result in ascending order based on the column firstname of the employee table.
mysql> SELECT * FROM employee ORDER BY firstname;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e05 | Bob | Coder | bobcoder@example.com | 7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
| e03 | Jane | Doe | janedoe@example.com | 9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e02 | John | Doe | johndoe@example.com | 8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e04 | Tin | Tin | tintin@example.com | 6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e01 | Yusuf | Shakeel | yusuf@example.com | 7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)
Note! we can also add the ASC keyword and we will get the same above result. But this is optional.
mysql> SELECT * FROM employee ORDER BY firstname ASC;
Sort in descending order using single column
In the following example we are sorting the result in descending order using column firstname of the employee table.
mysql> SELECT * FROM employee ORDER BY firstname DESC;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e01 | Yusuf | Shakeel | yusuf@example.com | 7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e04 | Tin | Tin | tintin@example.com | 6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e02 | John | Doe | johndoe@example.com | 8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03 | Jane | Doe | janedoe@example.com | 9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e05 | Bob | Coder | bobcoder@example.com | 7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)
Sort using multiple columns
In the following example we are sorting the employee detail from the employee table based on the score, firstname and lastname column.
We will sort the result in descending order based on the score.
And if the score of two or more employees is the same then, we will sort the result in ascending order by their firstname and lastname.
mysql> SELECT * FROM employee ORDER BY score DESC, firstname ASC, lastname ASC;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e03 | Jane | Doe | janedoe@example.com | 9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e02 | John | Doe | johndoe@example.com | 8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e05 | Bob | Coder | bobcoder@example.com | 7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
| e01 | Yusuf | Shakeel | yusuf@example.com | 7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e04 | Tin | Tin | tintin@example.com | 6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
5 rows in set (0.00 sec)
LIMIT
We use the LIMIT keyword to limit the number of rows fetched when we run a query.
Limit rows
Syntax:
SELECT column_name
FROM table_name
LIMIT row_count;
Where row_count is a non-negative integer value.
In the following example we are limiting to fetch only 3 rows from the employee table.
mysql> SELECT * FROM employee LIMIT 3;
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| e01 | Yusuf | Shakeel | yusuf@example.com | 7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02 | John | Doe | johndoe@example.com | 8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03 | Jane | Doe | janedoe@example.com | 9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
3 rows in set (0.01 sec)
Limit rows with offset
Syntax:
SELECT column_name
FROM table_name
LIMIT offset, row_count;
Where offset and row_count are non-negative integer value.
The offset is for the offset of the first row to return and row_count is the total number of rows to return.
Note! Offset of the initial row is 0.
Our employee table contains 5 rows. In the following example we will be fetching it in two pages were each page will hold max 3 rows.
So, for the first fetch we will use LIMIT 0, 3 and it will return us first 3 rows of the employee table.
In the next call we will use LIMIT 3, 3 and it will return the next 2 rows of the employee table.
For: LIMIT 0, 3
mysql> SELECT * FROM employee LIMIT 0, 3;
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
| e01 | Yusuf | Shakeel | yusuf@example.com | 7 | 1900-01-01 | 2018-02-04 06:08:10 | 2018-01-01 01:01:01 |
| e02 | John | Doe | johndoe@example.com | 8 | 1900-02-03 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
| e03 | Jane | Doe | janedoe@example.com | 9 | 1900-05-20 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 |
+------------+-----------+----------+---------------------+-------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)
For: LIMIT 3, 3
mysql> SELECT * FROM employee LIMIT 3, 3;
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| employeeid | firstname | lastname | email | score | birthday | lastmodified | created |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
| e04 | Tin | Tin | tintin@example.com | 6 | 1900-10-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 |
| e05 | Bob | Coder | bobcoder@example.com | 7 | 1900-08-20 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 |
+------------+-----------+----------+----------------------+-------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)
ADVERTISEMENT