Sorting Data with SQL – The ORDER BY Clause

Lesson 5: Sorting Data with SQL – The ORDER BY Clause

Objective: In this lesson, we will explore the ORDER BY clause in SQL. This clause is used to sort the result set of a query in either ascending or descending order based on one or more columns.

1. Introduction to the ORDER BY Clause

The ORDER BY clause in SQL is used to sort the records in a result set. You can sort the data in either ascending (ASC) or descending (DESC) order based on one or more columns.

Syntax:

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

By default, ORDER BY sorts the data in ascending order. You can use the DESC keyword if you want to sort in descending order.

2. Sorting Data in Ascending Order (ASC)

To sort data in ascending order (from the smallest to the largest), you can use the ASC keyword. If no order is specified, it will sort by default in ascending order.

Example:

To sort employees by their salary in ascending order:

SELECT * FROM Employees
ORDER BY Salary ASC;

Output:

EmployeeID  Name         Department  Salary
1          John Doe     HR          50000
2          Jane Smith   IT          60000

3. Sorting Data in Descending Order (DESC)

If you want to sort data from the largest to the smallest, you can use the DESC keyword.

Example:

To sort employees by their salary in descending order:

SELECT * FROM Employees
ORDER BY Salary DESC;

Output:

EmployeeID  Name         Department  Salary
2          Jane Smith   IT          60000
1          John Doe     HR          50000

4. Sorting by Multiple Columns

You can sort the result set by more than one column. The SQL query will first sort by the first column, and if there are any duplicate values, it will sort by the second column, and so on.

Example:

To sort employees first by department in ascending order and then by salary in descending order:

SELECT * FROM Employees
ORDER BY Department ASC, Salary DESC;

Output:

EmployeeID  Name         Department  Salary
1          John Doe     HR          50000
2          Jane Smith   IT          60000

5. Sorting with NULL Values

In SQL, NULL values are treated as the lowest possible values when sorting in ascending order. Conversely, when sorting in descending order, NULL values are treated as the highest possible values.

Example:

To sort employees by their salary and handle NULL values:

SELECT * FROM Employees
ORDER BY Salary ASC;

Output:

EmployeeID  Name         Department  Salary
1          John Doe     HR          50000
2          Jane Smith   IT          60000

6. Practice Exercise

Try these exercises to strengthen your understanding:

• Write a query to sort employees by their name in ascending order.
• Write a query to sort employees by their department in descending order and by salary in ascending order.
• Write a query to sort employees by salary from highest to lowest.

7. Conclusion

In this lesson, you learned how to sort data using the ORDER BY clause in SQL. Sorting is an essential skill for working with large datasets, as it helps you organize your data in a meaningful way.

By practicing with different sorting techniques and combining multiple columns, you can master how to efficiently sort and display data as per your requirements.

Key Points:

• The ORDER BY clause is used to sort data in SQL.
• By default, sorting is in ascending order; use DESC for descending order.
• You can sort data based on multiple columns.
• NULL values are sorted differently depending on the order (ASC or DESC).