ORDER BY Clause in SQL

Last Updated: 22nd June, 2023

Overview

The ORDER BY clause is utilized in a SELECT statement to sort the results based on one or more columns in ascending or descending order. It is the last clause in a SELECT statement and can be combined with the WHERE, GROUP BY, and HAVING clauses. The ORDER BY clause can be utlized to sort the results by one or more columns in either ascending (ASC) or descending (DESC) order.

ORDER BY Syntax

Akash was a database administrator with a small company. He had been tasked with creating an application allowing the company to query its database and retrieve certain information. Akash needed help to come up with the best way to sort the query results. He knew he wanted to sort the results in a particular order but needed to figure out how. He had heard about the ORDER BY clause in SQL but needed to figure out how to use it. After some research, Akash discovered that the ORDER BY clause was exactly what he needed. Let's help him with it.

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC;

This syntax is utilized to sort the results of a query in ascending or descending order based on the values in the specified columns. The SELECT statement is used to specify the columns in the result set, and the FROM clause is used to specify the table in which to search for the data. The ORDER BY clause is utilised to specify which column(s) to use for sorting and whether the sorting should be in ascending (ASC) or descending (DESC) order.

ORDER BY Example

SELECT *
FROM customers
ORDER BY customer_name ASC;

This query selects all columns from the customer's table and orders the results alphabetically by the customer_name column in ascending order (A-Z).

ORDER BY DESC Example

SELECT 
    first_name, last_name, age
FROM
    employees
ORDER BY age DESC;

This query selects the first name, last name, and age columns from the employees table and orders them by age in descending order.

ORDER BY Several Columns Example

SELECT id, name, age
FROM customers
ORDER BY name, age DESC;

This query will select the id, name, and age columns from the customers table and order the results by name in ascending order, followed by age in descending order.

Conclusion

With this clause, he could specify the exact order in which the results would be sorted. He was even able to specify multiple columns to sort by. Akash was so pleased with the results that he shared his story with other database administrators. Soon, ORDER BY clauses became a standard feature of SQL queries. Akash was proud to have played a role in popularizing this powerful tool.

Key takeaways

1. The ORDER BY clause is utilized to sort the records in a result set in either ascending or descending order.
2. ORDER BY must be the last clause in a SQL statement.
3. NULL values are placed last when sorted in ascending order and first when sorted in descending order.
4. The ORDER BY clause can use column names or numeric positions to specify the sorting order.
5. Multiple columns can be used for sorting by separating them with a comma.
6. The default sorting order is ascending. If a descending order is desired, the DESC keyword should be used.

Quiz

1. What is the correct syntax for the ORDER BY clause? 
   1. SELECT * FROM table ORDER BY column; 
   2. SELECT * FROM table SORT BY column; 
   3. SELECT * FROM table DISPLAY BY column; 
   4. SELECT * FROM table SORTED BY column;

Answer: A) SELECT * FROM table ORDER BY column;

2. What is the default order of data when using an ORDER BY clause? 
   1. Alphabetical 
   2. Ascending 
   3. Descending 
   4. Random

Answer: b. Ascending

3. What clause should be used with ORDER BY to ensure the query runs efficiently?  
   1. LIMIT 
   2. WHERE 
   3. TOP 
   4. INDEX

Answer: d. INDEX

4. Which of the following is not a valid order keyword in an ORDER BY clause?  
   1. ASC 
   2. DESC 
   3. ALPHABETICAL 
   4. RANDOM

Answer: d. RANDOM