PostgreSQL ORDER BY statement

PostgreSQL ORDER BY statement is used to sort records in your result set. ORDER BY can only be used in the SELECT operator.

The syntax for ORDER BY statement in PostgreSQL

SELECT_id
FROM tabs
[WHERE conds]
ORDER BY expression_id [ASC | DESC | USING operator] [NULLS FIRST | NULLS LAST];

Parameters and arguments of the statement

• expressions_id – The table or calculations that you want to get.
• tabs – The tables from which you want to get the records. The FROM operator must specify at least one table.
• WHERE conds – Optional. The conditions that must be met for the entries to be selected.
• ASC – Optional. It sorts the reuser set in ascending order (by default, if no modifier is specified).
• DESC is optional. It sorts the result set in descending order by.
• NULLS FIRST – Optional. If specified, all NULL values are sorted before values other than NULL in the resulting set.
• NULLS LAST – Optional. If specified, all NULL values are sorted after values other than NULL in the resulting set.

Note:

• If the ASC or DESC modifier is not specified in ORDER BY, the results will be sorted by expression in ascending order. This is equivalent to ORDER BY expression ASC.

Sorting without using the ASC / DESC attribute

PostgreSQL operator ORDER BY can be used without ASC or DESC modifier. When this attribute is omitted in ORDER BY operator, the sort order is set to ASC (in ascending order) by default.
For example:

SELECT last_name, first_name
FROM contacts
WHERE state = 'California'
ORDER BY last_name;

This example will return all records sorted by last_name field in ascending order and will be equivalent to the next ORDER BY operator:

SELECT last_name, first_name
FROM contacts
WHERE state = 'California'
ORDER BY last_name ASC;

Most programmers skip the ASC attribute when sorting in ascending order.

Sorting in descending order

When sorting a set of results in descending order, you use the DESC attribute in ORDER BY as follows:

SELECT last_name, first_name, city
FROM contacts
WHERE first_name = 'Nikol'
ORDER BY last_name DESC;

In this PostgreSQL example, ORDER BY will return all records sorted by the last_name field in descending order.

Sort by the relative position

You can also use PostgreSQL operator ORDER BY to sort by relative position (order position) in the result set, where the first field in the result set is 1. The next field equals 2, etc.
For example:

SELECT last_name, first_name, city
FROM contacts
WHERE first_name = 'Elena'
ORDER BY 3 DESC;

This PostgreSQL ORDER BY will return all records sorted by the city field in descending order since the city field is at position 3 in the resulting set and will be equivalent to the next ORDER BY:

SELECT last_name, first_name, city
FROM contacts
WHERE first_name = 'Elena'
ORDER BY city DESC;

Use of ASC and DESC attributes

When sorting a set of results with PostgreSQL operator ORDER BY you can use ASC and DESC attributes in one SELECT operator.
For example:

SELECT last_name, first_name, city
FROM contacts
WHERE first_name = 'Elena'
ORDER BY last_name ASC, first_name DESC;

This PostgreSQL ORDER BY will return all records sorted by last_name field in ascending order, with secondary sorting by first_name in descending order.

PostgreSQL: Order By | Course