PostgreSQL – ORDER BY clause

The PostgreSQL ORDER BY clause is used to sort the result query set returned by the SELECT statement. As the query set returned by the SELECT statement has no specific order, one can use the ORDER BY clause in the SELECT statement to sort the results in the desired manner.

Syntax:
SELECT
column_1,
column_2
FROM
table_name
ORDER BY
column_1 [ASC | DESC],
column_2 [ASC | DESC];

Let’s analyze the above syntax:

Firstly we specify the column or the expression that we want to be sorted. If multiple columns or expressions are to be used in the SELECT statement, we separate them with commas.
Secondly, The ASC expression is used to sort the result set in ascending order, and DESC expression is used to sort the result set in descending order. By default the ORDER BY clause uses ASC.

For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.

Example 1:
Using PostgreSQL ORDER BY clause to sort rows by one column in the “customer” table of the sample database

SELECT
    first_name,
    last_name
FROM
    customer
ORDER BY
    first_name DESC;

Output:

Example 2:
Using PostgreSQL ORDER BY clause to sort rows by multiple columns in the “customer” table. Here we will be sorting the customers by the first name in the ascending order first, and then sort the sorted result set by the last name in descending order.

SELECT
    first_name,
    last_name
FROM
    customer
ORDER BY
    first_name ASC,
    last_name DESC;

Output:

The output of the code gets more clear if the first name of the customer is the same as shown below:

Example 3:
Using PostgreSQL ORDER BY clause to sort rows by expressions. In the below example we will be using the ORDER BY clause with the LENGTH() function to sort the rows by the lengths of the first names of customers. The LENGTH() function accepts a string and returns its length.

SELECT 
    first_name,
    LENGTH(first_name) len
FROM
    customer
ORDER BY 
    LENGTH(first_name) DESC;

Output:

Article Tags :

Misc

postgreSQL-clauses