SQL ORDER BY Clause

---

In SQL, sorting data is often necessary to organize and present results in a meaningful way. Whether you're looking to display the highest-paid employees, the most recent orders, or the alphabetically sorted list of products, the ORDER BY clause in SQL allows you to sort query results in either ascending or descending order.

The ORDER BY clause is a powerful tool for controlling the order in which records appear in your query results. It is used to sort the data based on one or more columns. By default, the ORDER BY clause sorts data in ascending order, but you can also specify descending order.

---

1. Basic Syntax of the SQL ORDER BY Clause

The basic syntax for using the ORDER BY clause is straightforward. You specify the column or columns you want to sort by, followed by the order (ascending or descending).

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC];

• column_name: The column by which you want to sort the data.
• ASC (default): Sorts the data in ascending order.
• DESC: Sorts the data in descending order.
• table_name: The table from which you are retrieving data.

If no sort order is specified, SQL sorts the data in ascending order by default.

Example:

Let’s say you have a employees table, and you want to retrieve a list of employees ordered by their salary in ascending order:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary ASC;

Result:

first_name	last_name	salary
John	Doe	30000
Sarah	Lee	45000
Mike	Smith	60000

This query will return the employees sorted by their salary from the lowest to the highest.

---

2. Sorting in Descending Order

To sort the data in descending order, you can use the DESC keyword. This is useful when you want to see the highest values first, such as in a list of top salespeople or most expensive products.

Example:

Let’s retrieve a list of employees sorted by their salary in descending order:

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

Result:

first_name	last_name	salary
Mike	Smith	60000
Sarah	Lee	45000
John	Doe	30000

This query sorts the employees by salary, starting with the highest salary.

---

3. Sorting by Multiple Columns

You can sort your query results by more than one column. When you do this, SQL sorts the data based on the first column, then, if there are ties (i.e., duplicate values in the first column), it sorts by the second column, and so on.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Example:

Let’s say you have an employees table with columns for department and salary. You can first sort the employees by their department in ascending order and then by their salary in descending order within each department.

SELECT first_name, last_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Result:

first_name	last_name	department	salary
John	Doe	IT	60000
Sarah	Lee	IT	45000
Mike	Smith	HR	55000
Emily	Clark	HR	50000

This query first sorts by department (ascending order), and within each department, it sorts by salary in descending order.

---

4. Sorting by Expressions or Computed Columns

You can also use expressions or computed columns in the ORDER BY clause. For instance, if you want to sort the data based on the result of a calculation (e.g., the total price of products after applying a discount), you can use expressions directly in the ORDER BY clause.

Example:

Let’s assume you have a products table with columns for product_name, price, and discount_percentage. You want to sort the products by their discounted price in ascending order.

SELECT product_name, price, discount_percentage, (price - (price * discount_percentage / 100)) AS discounted_price
FROM products
ORDER BY discounted_price ASC;

Result:

product_name	price	discount_percentage	discounted_price
Widget	100	10	90
Gadget	200	15	170
Gizmo	150	20	120

In this query, SQL calculates the discounted price in the SELECT statement and then sorts the products based on this computed column, discounted_price.

---

5. Sorting with NULL Values

By default, when sorting data in SQL, NULL values are treated as the lowest possible values when using ascending order (ASC) and as the highest values when using descending order (DESC).

• In ascending order (ASC), NULL values come first.
• In descending order (DESC), NULL values come last.

Example:

Let’s retrieve a list of employees ordered by their hire_date, with NULL values (if any) appearing at the end of the list:

SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC NULLS LAST;

This query will return the employees sorted by their hire date, with the NULL values appearing at the end of the list. (Note that some SQL database systems like PostgreSQL support NULLS LAST and NULLS FIRST explicitly.)

---

6. Combining ORDER BY with LIMIT / FETCH FIRST

When retrieving a large dataset, you might want to limit the number of rows returned. The ORDER BY clause can be combined with the LIMIT (in MySQL, PostgreSQL) or FETCH FIRST (in SQL Server and Oracle) to return a sorted subset of rows.

Syntax:

For MySQL and PostgreSQL:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number;

For SQL Server and Oracle:

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC]
FETCH FIRST number ROWS ONLY;

Example:

Let’s say you want to retrieve the top 5 highest-paid employees from the employees table:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

This query will return the top 5 highest-paid employees.

---

---