PostgreSQL ORDER BY: Ascending and Descending Sorting

Introduction

Understanding the PostgreSQL ORDER BY clause is essential for effectively organizing database query results. This tutorial will guide you through basic to advanced usage with practical code examples.

Sorting Basics with ORDER BY

In PostgreSQL, sorting query results is primarily done using the ORDER BY clause. Sorting can be either ascending (ASC) or descending (DESC).

SELECT column1 FROM table_name ORDER BY column1 ASC;

This code snippet selects all values from column1 and sorts them in ascending order. To sort in descending order:

SELECT column1 FROM table_name ORDER BY column1 DESC;

Sorting by Multiple Columns

ORDER BY can also sort by multiple columns. First, it sorts by column1, then within those results, it sorts by column2.

SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;

Sorting with NULLS FIRST/LAST

The presence of null values can affect sort order. Use NULLS FIRST or NULLS LAST to control their placement.

SELECT column1 FROM table_name ORDER BY column1 ASC NULLS FIRST;

Sorting Using Expressions

You can sort by expressions or functions for more complex ordering.

SELECT * FROM table_name ORDER BY LENGTH(column1) DESC;

Sorting and Limiting Results

Combining ORDER BY with LIMIT controls the quantity of records returned, useful for pagination.

SELECT * FROM table_name ORDER BY column1 ASC LIMIT 10;

Sorting with Case Insensitivity

To sort strings without considering case sensitivity, use the LOWER or UPPER function:

SELECT * FROM table_name ORDER BY LOWER(column1) ASC;

Sorting by Aliased Columns

It is possible to sort by a column alias introduced in the SELECT clause.

SELECT column1 AS c1 FROM table_name ORDER BY c1 DESC;

Sorting in Subqueries and Joins

Sorting can come into play in more complex queries, such as those that include subqueries and joins.

SELECT * FROM (SELECT column1, column2 FROM table_name) AS sub_query ORDER BY column2 ASC;

Sorting with Aggregated Data

When dealing with aggregated data, the ORDER BY clause sorts the results based on aggregated columns.

SELECT COUNT(*), column1 FROM table_name GROUP BY column1 ORDER BY COUNT(*) DESC;

Using ORDER BY in Window Functions

In window functions, the order of rows is significant. The ORDER BY clause controls the order in which functions are applied.

SELECT column1, SUM(column2) OVER (ORDER BY column1) FROM table_name;

Conclusion

From basic sorting to advanced usage in joins and window functions, mastering PostgreSQL’s ORDER BY clause allows you to query and organize data effectively. With these examples, you’re well on your way to becoming proficient in sorting SQL query results.