Home » Core Java » sql » SQL Order By Clause Example

About Reshma Sathe

I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.

SQL Order By Clause Example

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:

Example 1: Numeric Sort
SELECT * FROM FILM_LIST ORDER BY LENGTH

Example 2
SELECT * FROM SALES_BY_FILM_CATEGORY ORDER BY CATEGORY;
SQL Order By - Default sort order
Default Sort Order

5.2 Ascending Order sort

We can specify the sorting order as the Ascending order that is smallest value to largest value. To sort records in a column in the 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

#EXAMPLE 1 : ALPHABETIC/LEXICAL SORT
SELECT * FROM ADDRESS ORDER BY DISTRICT ASC;

#EXAMPLE 2 : NUMERIC SORT
SELECT * FROM ADDRESS ORDER BY CITY_ID ASC;
SQL Order By - Order by ASC
Order by ASC

5.3 Descending Order sort

To sort records in the 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:

#EXAMPLE 1: NUMERIC SORT
SELECT * FROM CITY ORDER BY COUNTRY_ID DESC;

#EXAMPLE 2: ALPHABETICAL/LEXICAL SORT
SELECT * FROM CITY ORDER BY CITY DESC;
SQL Order By - Order by DESC
Order by 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 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 none of the titles are the same and hence there are no conflicts.

 SELECT * FROM FILM ORDER BY TITLE , RATING ;
SQL Order By - Multiple Columns Sort with Default sort order
Multiple Columns Sort with Default sort order

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 none of the titles are the same and hence there are no conflicts. However, if there were conflicts then mysql will sort the records in the descending order for the rating column.

SELECT * FROM FILM ORDER BY TITLE, RATING DESC;
First column default and second column DESC
First column default and second column 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 the descending order for rating.

SELECT * FROM FILM ORDER BY TITLE DESC, RATING DESC;
Both columns DESC
Both Columns 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;
Order by First DESC, second default
Order by First DESC, second default

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;
Conflicts first desc and second default
Conflicts first desc and second default

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;
Conflicts and first desc and second asc
Conflicts and first desc and second asc

5.4.7 Example 7: Last_name descending order and First_name descending order.

The 2 records marked in red will sorted in the descending order of the First_name.

SELECT * FROM CUSTOMER ORDER BY LAST_NAME DESC, FIRST_NAME DESC;
Conflicts and both col desc
Conflicts and both col 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 the 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;
Order by first default,second desc,third asc
Order by first default,second desc,third 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 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;
Order by first desc,second desc and third asc
Order by first desc,second desc and third 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 is available here.

7. Download the Source Code

This was an example of the Order by Clause using MySQL RDBMS.

Download
You can download the full source code of this example here: SQL Order By Clause Example

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments