How to Order by Clause in PostgreSQL

1. PostgreSQL ORDER BY Clause
2. Use of PostgreSQL ORDER BY Clause
3. Code Examples Demonstrating the Use of PostgreSQL ORDER BY Clause

Today’s article educates how to sort the result from a table based on the attributes of that table. It also demonstrates how we can use the ORDER BY clause to sort data. Let’s start with understanding this command.

PostgreSQL ORDER BY Clause

To understand it, let’s discuss the SELECT statement. When we write a SELECT statement in a query, it gives us all the data based on the statement from the specified table.

That resultant data returns without any specified order. What do we do if we desire the data to be in a specific order? We use the ORDER BY command with the SELECT statement.

The ORDER BY command allows us to sort the data based on one or multiple attributes from the table. We can also sort in ascending or descending order using that set of attributes.

Now we have understood the PostgreSQL ORDER BY command, let’s move toward its use.

Use of PostgreSQL ORDER BY Clause

The ORDER BY command is written after the SELECT and FROM statements along with the rest of the query. There are a few essential details that we need to know to use this command.

First, decide what column or set of columns will be used as our parameter for ordering the results. Remember, it depends on the scope of the problem we are trying to solve.

We will explain it using examples later in this article. The important syntactical detail to remember is that we use a comma (,) if multiple attributes are to be used as parameters.

The Syntax for Single Parameter:

ORDER BY column_name;

The Syntax for Multiple Parameters:

ORDER BY column1_name, column2_name;

Remember, if multiple parameters are used, the ORDER BY clause first checks the dataset according to the first parameter.

If two values have the exact ordering according to the first parameter, then the second parameter is used to eliminate that similarity and sort the resultant data.

Second, we can sort the resulting data based on attributes in ascending or descending order. The keywords ASC and DESC define whether to sort in ascending or descending order, respectively.

If we have not specified either, it will be ordered in ascending order by default.

The Syntax for Sorting in Ascending Order:

ORDER BY column1_name ASC;

The Syntax for Sorting in Descending Order:

ORDER BY column1_name DESC;

The Syntax for Sorting in Ascending and Descending Order Together:

ORDER BY column1_name ASC, column2_name DESC;

Lastly, another case is when there is NULL data in the attribute/column used as parameters by the ORDER BY clause. We can specify whether to put the NULL values before or after other values.

The keywords NULLS LAST and NULLS FIRST are used for this operation. If the ASC option is used, then NULLS LAST is selected by default.

On the other hand, if the DESC option is used, then NULLS FIRST is selected by default. We can change it according to what is required by adding the keywords.

The Syntax for Putting NULL Values First:

ORDER BY column1_name ASC NULLS FIRST;

The Syntax for Putting NULL Values at the End:

ORDER BY column1_name DESC NULLS LAST;

Code Examples Demonstrating the Use of PostgreSQL ORDER BY Clause

For illustration through examples, we first create a table as follows:

create table Orders(
    id int,
    name varchar(30) not null,
    OrderAmount int not null,
    constraint pk_customer primary key (id)
);

insert into Orders 
values
(1,'Ben', 250),
(2, 'James', 350),
(3, 'Carl', 550),
(4, 'Adam', 550);

select * from Orders;

Output:

Now the table is created, let’s demonstrate all the possible ways (discussed above) to use the ORDER BY command.

PostgreSQL ORDER BY Command Using One Expression

The following code will display the names of customers and their total expenditure from the Orders table sorted by the price of the orders:

for Ascending Order of OrderAmount

SELECT name, OrderAmount
FROM Orders
ORDER BY OrderAmount ASC;

Output:

for Descending Order of OrderAmount

SELECT name, OrderAmount
FROM Orders
ORDER BY OrderAmount DESC;

Output:

PostgreSQL ORDER BY Command Using Multiple Expressions

The following code displays customers’ names and their total expenditure from the Orders table sorted by the price and customer name of the orders.

Sort in Ascending Order

SELECT name, OrderAmount
FROM Orders
ORDER BY OrderAmount, name;

Output:

Sort in Descending Order

SELECT name, OrderAmount
FROM Orders
ORDER BY OrderAmount DESC, name DESC;

Output:

Both Ascending and Descending

SELECT name, OrderAmount
FROM Orders
ORDER BY OrderAmount DESC, name ASC;

Output:

PostgreSQL ORDER BY Command With NULL Attribute Values

We will use a new table with null values for this example given below.

create table NULLTEST(
    name varchar(30) not null,
    number_ int
);

insert into NULLTEST
values 
('Jack', 250),
('Maguire', 350),
('Walker', 550),
('Michael', NULL);

select * from NULLTEST;

Output:

The following code will display name and number_ sorted by the values in the number_ column with any NULL values first.

SELECT name, number_
FROM NULLTEST
ORDER BY number_ ASC NULLS FIRST;

Output:

Using NULL FIRST with the descending order option of ORDER BY is unnecessary as any NULL value will appear first by default in this case. The following code will display name and number_ sorted by the values in the number_ column with any NULL values.

SELECT name, number_
FROM NULLTEST
ORDER BY number_ DESC NULLS LAST;

Here, using NULLS LAST with the ascending order option of ORDER BY is unnecessary as any NULL value will appear at last by default in this case.