1. Introduction
In this article, we will look at the Order by Clause. The Order by Clause sorts records returned by Select Queries. It’s an optional clause and can sort records with single or multiple tables.
2. Order by Clause in Relational Algebra
Relational Algebra considers relations assets(auto-sorted). Hence, there is no concept of sorting or ordering there. However, SQL considers relations as bags that require sorting. To accommodate SQL, relational algebra was extended to include the tau(τ) operator. The tau(τ) operator converts the input relation into a bag and outputs the records or tuples in sorted order. The basic syntax of using the order by in relational algebra is:
τ [attribute-list][ASC or DESC](Relation)
where τ = order by clause
attribute-list = column names for Order by
Relation = table name
3. SQL Order by Clause in MySQL
Select Queries have an optional Order by clause. This is the last clause in a query. Subqueries can have an Order by Clause too and so can query with multiple tables in them.
The Basic syntax of Order By Clause is as follows:
SELECT field1, field2,... FROM target_table ORDER BY field1 [ASC|DESC], field2 [ASC|DESC],...
- field1, field2 = list of columns for Order By.
- ASC/DESC = sort order. ASC = Ascending and DESC = Descending
Ascending Order is the default sort order.
4. Setup for examples of Order by
Forgoing through the examples related to the Order y Clause, we will consider the database called “Sakila”, an example database by MySQL. The schema structure is available on the documentation page.
For running the queries, we will use the MySQL Workbench. The documentation for MySQL Workbench is available here.
5. Examples with Order by Clause
We will see various examples with the Order by clause. these are simple examples and a lot more variation is possible.
5.1 Order by default
To sort records in a table we need to use the Order by clause along with the name of the column and the order i.e. Ascending or Descending. If no order is specified then MySQL sorts the records in ascending order by default. MySQL sorts String or text values lexicographically.
Some simple Queries showing this are as follows:
SELECT * FROM FILM_LIST ORDER BY LENGTH
SELECT * FROM SALES_BY_FILM_CATEGORY ORDER BY CATEGORY;
5.2 Ascending Order sort
We can specify the sorting order as the Ascending order that is the smallest value to largest value. To sort records in a column in Ascending order, we need to specify the column name followed by the keyword ASC. Writing ASC is entirely optional since the default sorting order is ascending. For columns with String/text values, the records are sorted lexicographically. In such cases, Null Values are taken first and then the records with actual values are sorted. Examples are as follows
SELECT * FROM ADDRESS ORDER BY DISTRICT ASC;
SELECT * FROM ADDRESS ORDER BY CITY_ID ASC;
5.3 Descending Order sort
To sort records in Descending order, we need to specify the DESC keyword in front of the column name. Descending sort for String/text values pushes the Null and empty values at the very end. Examples are as follows:
SELECT * FROM CITY ORDER BY COUNTRY_ID DESC;
SELECT * FROM CITY ORDER BY CITY DESC;
5.4 Order by using multiple columns
We can sort records using more than one column in a table. We need to specify the sort order for each of the columns specified. If we do not specify a sort order, then by default MySQL sorts using the Ascending order. In the case of multiple columns, then MySQL sorts the records on the basis of the first column first. Mysql will consider the second, third and so forth columns mentioned only for conflicts. Otherwise, MySQL will ignore the rest of the columns in the Order by clause.
5.4.1 Example 1: Only first column considered, Default Sort order for both columns
In this example, MySQL will sort the records based on the Title column only in the default i.e. Ascending order. Mysql will not consider the rating column since any of the titles are the same and hence there are no conflicts.
SELECT * FROM FILM ORDER BY TITLE , RATING ;
5.4.2 Example2: Only first column considered, Default sort order for the first column and the desc order for the second
Here again, MySQL sorts the records based on the Title column only in the default i.e. Ascending order. MySQL will not consider the rating column since any of the titles are the same and hence there are no conflicts. However, if there were conflicts then MySQL will sort the records in descending order for the rating column.
SELECT * FROM FILM ORDER BY TITLE, RATING DESC;
5.4.3 Example 3: Descending order for both the columns.
The sort order for the second column does not matter since it is never considered. However, in case of conflicts mysql will sort the records in descending order for rating.
SELECT * FROM FILM ORDER BY TITLE DESC, RATING DESC;
5.4.4 Example 4: Descending order for first column and default sort order for the second column
Here, MySQL sorts the records in the descending order of the Title column. In case of conflicts, MySQL will sort the records by Default i.e. Ascending Order for Rating.
SELECT * FROM FILM ORDER BY TITLE DESC, RATING;
5.4.5 Example 5: Last_name descending order and default order for First_name
Here, there are 2 records that are marked in red in the image below that have the same Last_name. In this case, MySQL will sort the records using First_name column in the default i.e. Ascending order.
SELECT * FROM CUSTOMER ORDER BY LAST_NAME DESC,FIRST_NAME;
5.4.6 Example 6: Last_name descending order and Ascending order for First_name
The 2 records marked in red in the image below have the same Last_name. MySql will sort the records using the First_name in the Ascending order.
SELECT * FROM CUSTOMER ORDER BY LAST_NAME DESC,FIRST_NAME ASC;
5.4.7 Example 7: Last_name descending order and First_name descending order.
The 2 records marked in red will be sorted in the descending order of the First_name.
SELECT * FROM CUSTOMER ORDER BY LAST_NAME DESC, FIRST_NAME DESC;
5.5 Order by for multiple tables and multiple columns.
We can sort records from multiple tables across multiple columns too. In this case, too, MySQL sorts the records using the first column first and then the second column and so on.
5.5.1 Example 1 : Records with conflicts and first column default, second descending and third ascending order
In the below example, MySQL sorts the records in the ascending order of the name i.e. first column. In case of conflicts, like in the first 2 records where the name column has the same value for both records, MySQL will sort the records using the length in descending order. Here again, the first 2 records have the same length and so in this case, MySQL will sort the records in the ascending order of the title column.
SELECT F.FILM_ID,F.TITLE,C.NAME,F.LENGTH,F.DESCRIPTION,F.RENTAL_RATE,F.RATING,GROUP_CONCAT(CONCAT(A.FIRST_NAME," ",A.LAST_NAME) SEPARATOR ', ') AS "ACTORS" FROM CATEGORY C, FILM_CATEGORY FC,FILM F, FILM_ACTOR FA,ACTOR A WHERE C.CATEGORY_ID = FC.CATEGORY_ID AND FC.FILM_ID = F.FILM_ID AND F.FILM_ID = FA.FILM_ID AND FA.ACTOR_ID = A.ACTOR_ID GROUP BY F.TITLE ORDER BY C.NAME ,F.LENGTH DESC,F.TITLE ASC;
5.5.2 Example 2: Records with conflicts and first and second column descending and third column ascending order.
Here again for the first 2 records and also the records 3 and 4, the name is the same for the records. Hence, MySQL sorts the records in the descending order of the Length column. Here, the Length is the same and so MySQL will use ascending order sort for the title column.
SELECT F.FILM_ID,F.TITLE,C.NAME,F.LENGTH,F.DESCRIPTION,F.RENTAL_RATE,F.RATING,GROUP_CONCAT(CONCAT(A.FIRST_NAME," ",A.LAST_NAME) SEPARATOR ', ') AS "ACTORS" FROM CATEGORY C, FILM_CATEGORY FC,FILM F, FILM_ACTOR FA,ACTOR A WHERE C.CATEGORY_ID = FC.CATEGORY_ID AND FC.FILM_ID = F.FILM_ID AND F.FILM_ID = FA.FILM_ID AND FA.ACTOR_ID = A.ACTOR_ID GROUP BY F.TITLE ORDER BY C.NAME DESC,F.LENGTH DESC,F.TITLE ASC;
6. Summary
In the article, we saw syntaxes related to the Order by Clause in MySQL. We sort the returned records by using Order by clause. MySQL optimizes Order by clause just like the Where Clause. The rules for MySQL optimization for Order By clause are available here.
7. Download the Source Code
This was an example of the Order by Clause using MySQL RDBMS.
You can download the full source code of this example here: SQL Order By Clause Example
Last updated on Nov. 07th, 2021
