PostgreSQL: ORDER BY Clause – Sorting Data

Welcome to The Coding College, your trusted source for coding and programming tutorials! In this article, we’ll explore the ORDER BY clause in PostgreSQL, which allows you to sort query results effectively for better data organization and analysis.

What is the ORDER BY Clause?

The ORDER BY clause is used to sort rows in a query result based on one or more columns. Sorting can be done in ascending (ASC) or descending (DESC) order, making it easy to organize data in a meaningful way.

Syntax

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

• column1, column2: Columns to sort by.
• ASC: Ascending order (default).
• DESC: Descending order.

Example: Sample Table

Let’s use a table named employees for demonstration:

Sorting Data

Example 1: Sort by Salary in Ascending Order

SELECT name, salary
FROM employees
ORDER BY salary ASC;

Result:

Example 2: Sort by Age in Descending Order

SELECT name, age
FROM employees
ORDER BY age DESC;

Result:

Sorting by Multiple Columns

When sorting by multiple columns, PostgreSQL first sorts by the first column and then by the second column in the specified order.

Example 3: Sort by Department (Ascending) and Salary (Descending)

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

Result:

Sorting with Aliases

You can use column aliases in the ORDER BY clause if your query includes expressions.

Example 4: Sort by Calculated Values

SELECT name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

Result:

Sorting Null Values

PostgreSQL places NULL values at the end of the result set by default. You can explicitly control their order using NULLS FIRST or NULLS LAST.

Example 5: Sort by Age with NULL Values First

SELECT name, age
FROM employees
ORDER BY age ASC NULLS FIRST;

Combining ORDER BY with Other Clauses

Example 6: Sort Filtered Results

SELECT name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

Result:

Learn More at The Coding College

For more in-depth PostgreSQL tutorials and guides, visit The Coding College. We adhere to Google’s E-E-A-T principles, ensuring our content is expert, accurate, and focused on user benefit.

Conclusion

The ORDER BY clause in PostgreSQL is a fundamental feature for sorting data in your queries. By mastering its usage, you can enhance the readability and usefulness of your query results.

For more SQL tips, tricks, and tutorials, stay connected with The Coding College!