The basic syntax for the ORDER BY clause is as follows:

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

• column1, column2, …: The columns you want to retrieve in the result set.
• FROM table: The table from which you are retrieving the data.
• ORDER BY: Keyword indicating the start of the sorting specification.
• column1 [ASC | DESC], column2 [ASC | DESC], …: The columns by which you want to sort the result set. ASC (ascending) is the default order, but you can use DESC (descending) for reverse sorting.

Examples:

Let’s delve into a few examples to illustrate the use of the ORDER BY clause:

1.  Sorting with a Single Column:

The simplest use case of ORDER BY involves sorting the result set based on a single column. For instance, if you have a table of products and want to retrieve them in alphabetical order by name, the query would look like this:

SELECT product_name, price
FROM products
ORDER BY product_name ASC;

In this example, the result set will be sorted in ascending order based on the product_name column.

2. Sorting with Multiple Columns:

The real power of ORDER BY becomes evident when you need to sort by multiple columns. This is useful for scenarios where you want a primary sort on one column and a secondary sort on another. Consider a scenario where you have a table of employees, and you want to sort them first by department and then by salary:

SELECT employee_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

In this case, the result set will be sorted in ascending order based on the department column, and within each department, the salary will be sorted in descending order.

3. Sorting NULL Values:

Dealing with NULL values requires special attention when using ORDER BY. By default, NULL values are treated as the highest possible value when sorting in ascending order and the lowest possible value when sorting in descending order. However, you can customize this behavior using the NULLS FIRST or NULLS LAST options:

SELECT column1, column2, ...
FROM table
ORDER BY column1 [ASC | DESC] NULLS FIRST | NULLS LAST, ...;