PostgreSQL ORDER BY

PostgreSQL ORDER BY

By default, a database does not return results in any specific, guaranteed order. To sort your result set systematically, you must use the ORDER BY keyword.

The ORDER BY clause sorts the fetched records in ascending or descending order. It is incredibly useful for creating organized lists, like alphabetized usernames.

The Default Sorting Order

The ORDER BY keyword heavily favors ascending order by default. If you do not specify a sorting direction, it sorts from A-Z or lowest to highest.

You use the ASC keyword to explicitly request an ascending sort. However, since it is the default behavior, writing ASC is entirely optional.

-- Sorts all customers alphabetically by their Country name
SELECT * FROM Customers
ORDER BY Country;

Sorting in Descending Order

To reverse the sorting direction, you must explicitly use the DESC keyword. DESC stands for descending, meaning Z-A or highest number to lowest number.

This is perfectly suited for fetching the most recent dates or highest prices. You place the DESC keyword immediately after the designated column name.

-- Sorts products from the most expensive down to the cheapest
SELECT * FROM Products
ORDER BY Price DESC;

Sorting by Multiple Columns

You can easily sort your result set by multiple columns simultaneously. You simply separate the column names with a comma in the ORDER BY clause.

The database will sort by the first column first, and then evaluate the second. If the first column has matching values, the second column resolves the tie.

-- Sorts by Country first, then by CustomerName for ties
SELECT * FROM Customers
ORDER BY Country, CustomerName;

Mixing ASC and DESC

When sorting by multiple columns, you can define different directions for each. You can sort the first column ascending, and the second column descending.

This allows for highly complex and incredibly specific data organization. For example, sorting by Country ascending, but filtering highest salaries first.

Summary

The ORDER BY clause brings clean predictability to your data presentation. Use ASC for standard low-to-high sorting, and DESC for high-to-low sorting.

Always combine it with multiple columns when dealing with vast duplicate datasets.