Sorting Data with ORDER BY in SQL | SQL ORDER BY Tutorial

Introduction

When you retrieve data from a database, the results are not always in a meaningful order. To organize the output, you use the ORDER BY clause in SQL.

The ORDER BY clause lets you sort the result set based on one or more columns, either in ascending or descending order.

In this section, you'll learn how to use ORDER BY effectively to make your SQL queries more powerful and your data more readable.




What is ORDER BY in SQL?

The ORDER BY clause sorts the records returned by a SQL query based on one or more columns.



Basic Syntax:


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

ASC = Ascending order (default).
DESC = Descending order.




Sorting in Ascending Order (Default)


When you don't specify anything, ORDER BY sorts the result in ascending order.

Example:

Retrieve all employees sorted alphabetically by their first name:

SELECT first_name, last_name
FROM employees
ORDER BY first_name;

Result: Employees will be listed from A to Z by their first name.




Sorting in Descending Order

If you want to reverse the sorting order (Z to A, or highest to lowest for numbers), use DESC.



Example:


Retrieve all products sorted by price from highest to lowest:

SELECT product_name, price
FROM products
ORDER BY price DESC;

Result: Products will appear from the most expensive to the least expensive.




Sorting by Multiple Columns

You can sort by multiple columns by separating them with commas. The data will be sorted by the first column, and if there are duplicates, it will sort by the next column.



Example:


Retrieve employees sorted by department (A-Z), and within each department by salary (highest first):

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

Explanation:

  • First, employees are grouped alphabetically by department.
  • Then, within each department, employees are sorted by salary from highest to lowest.



Using Column Numbers in ORDER BY

Instead of writing column names, you can use column numbers based on the position in the SELECT statement.



Example:


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

Here, 3 refers to the third selected column (salary), and the results will be sorted by salary in descending order.

Note: Using column numbers is allowed, but not recommended for complex queries because it can make your SQL harder to read and maintain.




ORDER BY with WHERE Clause

You can filter data with a WHERE clause and then sort it with ORDER BY.



Example:


Retrieve employees from the "Marketing" department and sort them by salary:

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



ORDER BY and NULL Values

Different databases handle NULL values differently when sorting:

  • In ascending order, NULLs often appear first.
  • In descending order, NULLs often appear last.

Some databases (like PostgreSQL) allow you to explicitly specify:

ORDER BY column_name ASC NULLS LAST


Example (PostgreSQL-specific):


SELECT name, manager_id
FROM employees
ORDER BY manager_id ASC NULLS LAST;



Conclusion

The ORDER BY clause is essential for presenting your SQL query results in a logical and readable format. Whether you're listing products by price, employees by salary, or customers alphabetically, mastering ORDER BY gives you full control over how your data appears.