Sorting Data with ORDER BY in SQL

Sorting Data with ORDER BY in SQL

The ORDER BY clause in SQL is used to sort the result set of a query based on one or more columns. You can sort data in either ascending (ASC) or descending (DESC) order. Sorting data is useful when you want to organize your results in a specific order, such as sorting a list of employees by salary or arranging products by their names.

---

1. Basic Syntax of ORDER BY

The ORDER BY clause is added at the end of a SELECT statement to specify how the data should be sorted.

Syntax:

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: Sorts the data in ascending order (default).

• DESC: Sorts the data in descending order.

---

2. Sorting in Ascending Order

By default, the ORDER BY clause sorts the data in ascending order (ASC). This means that numeric values will be sorted from the smallest to the largest, and text values will be sorted alphabetically from A to Z.

Example:

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

In this example:

• The SELECT statement retrieves the first_name, last_name, and salary columns from the employees table.

• The ORDER BY clause sorts the results in ascending order based on the salary column.

Result:

first_name	last_name	salary
Neha	Singh	45000.00
Amit	Sharma	55000.00
Ravi	Verma	60000.00

In this output:

• The employees are listed in order of their salary, starting with the lowest salary.

Note: When sorting text columns, the sorting is case-insensitive by default in most SQL databases.

---

3. Sorting in Descending Order

To sort data in descending order, you use the DESC keyword. This means that numeric values will be sorted from the largest to the smallest, and text values will be sorted alphabetically from Z to A.

Example:

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

In this example:

• The SELECT statement retrieves the first_name, last_name, and salary columns from the employees table.

• The ORDER BY clause sorts the results in descending order based on the salary column.

Result:

first_name	last_name	salary
Ravi	Verma	60000.00
Amit	Sharma	55000.00
Neha	Singh	45000.00

In this output:

• The employees are listed in order of their salary, starting with the highest salary.

---

4. Sorting by Multiple Columns

You can sort the result set by more than one column. When sorting by multiple columns, the result set is first sorted by the first column specified, and then within that sorting, it is sorted by the second column.

Example:

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

In this example:

• The SELECT statement retrieves the first_name, last_name, department, and salary columns from the employees table.

• The ORDER BY clause first sorts the results by the department column in ascending order and then by the salary column in descending order within each department.

Result:

first_name	last_name	department	salary
Amit	Sharma	HR	55000.00
Neha	Singh	IT	60000.00
Ravi	Verma	IT	50000.00

In this output:

• The employees are first sorted by their department names in ascending order. Within the HR department, there is only one employee, so no further sorting is needed. For the IT department, the employees are sorted by salary in descending order.

---

5. Sorting by Column Position

In addition to sorting by column names, you can also sort by the position of the columns in the SELECT statement. This is less common but can be useful in specific situations.

Example:

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

In this example:

• The ORDER BY 3 DESC sorts the result set based on the third column in the SELECT list, which is salary, in descending order.

---

6. Combining Sorting with Filtering

You can combine the ORDER BY clause with the WHERE clause to filter and then sort the data. This allows you to retrieve only the relevant data and then arrange it in a specific order.

Example:

SELECT first_name, last_name, salary 
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;

In this example:

• The WHERE clause filters the rows to include only employees in the IT department.

• The ORDER BY clause sorts the filtered results by salary in descending order.

Result:

first_name	last_name	salary
Neha	Singh	60000.00
Ravi	Verma	50000.00

---

7. Handling NULL Values in Sorting

When sorting data that includes NULL values, different SQL implementations may handle NULL differently. Generally, NULL values are treated as the lowest possible value when sorting in ascending order, and as the highest possible value when sorting in descending order.

Example:

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

In this example:

• If there are NULL values in the salary column, they will appear at the top of the result set when sorting in ascending order.

Result:

first_name	last_name	salary
Anjali	Patel	NULL
Neha	Singh	45000.00
Amit	Sharma	55000.00

In this output:

• Anjali Patel has a NULL salary, which appears first in the sorted result set.

---

Conclusion

The ORDER BY clause is a versatile tool in SQL that allows you to organize the results of your queries in a specific order. Whether sorting by a single column, multiple columns, or even handling NULL values, mastering the ORDER BY clause is essential for effective data querying and reporting.

This tutorial is brought to you by codes with pankaj.