Introduction to PostgreSQL ORDER BY clause

When you query data from a table, theSELECT statement returns rows in an unspecified order. To sort the rows of the result set, you use theORDER BY clause in theSELECT statement.

TheORDER BY clause allows you to sort rows returned by a SELECT clause in ascending or descending order based on a sort expression.

The following illustrates the syntax of theORDER BY clause:

SELECT  select_listFROM  table_nameORDER BY  sort_expression1 [ASC| DESC],  sort_expression2 [ASC| DESC],  ...;

In this syntax:

• First, specify a sort expression, which can be a column or an expression, that you want to sort after theORDER BY keywords. If you want to sort the result set based on multiple columns or expressions, you need to place a comma (,) between two columns or expressions to separate them.
• Second, you use the ASC option to sort rows in ascending order and theDESC option to sort rows in descending order. If you omit theASC orDESC option, theORDER BY usesASC by default.

PostgreSQL evaluates the clauses in theSELECT statement in the following order:FROM,SELECT, andORDER BY:

Due to the order of evaluation, if you have a column alias in theSELECT clause, you can use it in theORDER BY clause.

Let’s take some examples of using the PostgreSQLORDER BY clause.

PostgreSQL ORDER BY examples

We will use thecustomer table in thesample database for the demonstration.

1) Using PostgreSQL ORDER BY clause to sort rows by one column

The following query uses theORDER BY clause to sort customers by their first names in ascending order:

SELECT  first_name,  last_nameFROM  customerORDER BY  first_nameASC;

Since theASC option is the default, you can omit it in theORDER BY clause like this:

SELECT  first_name,  last_nameFROM  customerORDER BY  first_name;

2) Using PostgreSQL ORDER BY clause to sort rows by one column in descending order

The following statement selects the first name and last name from thecustomer table and sorts the rows by values in the last name column in descending order:

SELECT  first_name,  last_nameFROM  customerORDER BY  last_nameDESC;

3) Using PostgreSQL ORDER BY clause to sort rows by multiple columns

The following statement selects the first name and last name from the customer table and sorts the rows by the first name in ascending order and last name in descending order:

SELECT  first_name,  last_nameFROM  customerORDER BY  first_nameASC,  last_nameDESC;

In this example, the ORDER BY clause sorts rows by values in the first name column first. Then it sorts the sorted rows by values in the last name column.

As you can see clearly from the output, two customers with the same first nameKelly have the last name sorted in descending order.

4) Using PostgreSQL ORDER BY clause to sort rows by expressions

TheLENGTH() function accepts a string and returns the length of that string.

The following statement selects the first names and their lengths. It sorts the rows by the lengths of the first names:

SELECT  first_name,  LENGTH(first_name) lenFROM  customerORDER BY  lenDESC;

Because theORDER BY clause is evaluated after theSELECT clause, the column aliaslen is available and can be used in theORDER BY clause.

PostgreSQL ORDER BY clause and NULL

In the database world,NULL is a marker that indicates the missing data or the data is unknown at the time of recording.

When you sort rows that containNULL, you can specify the order ofNULL with other non-null values by using theNULLS FIRST orNULLS LAST option of theORDER BY clause:

ORDER BY sort_expresssion [ASC | DESC] [NULLS FIRST | NULLS LAST]

TheNULLS FIRST option placesNULL before other non-null values and theNULL LAST option placesNULL after other non-null values.

Let’screate a table for the demonstration.

-- create anew tableCREATE TABLEsort_demo(numINT);-- insert some dataINSERT INTOsort_demo(num)VALUES  (1),  (2),  (3),  (null);

Note that if you are not yet familiar with theCREATE TABLE andINSERT statements, you can simply execute them frompgAdmin orpsql to create thesort_demo table and insert data into it.

The following query returns data from thesort_demo table:

SELECT  numFROM  sort_demoORDER BY  num;

Output:

num------    1    2    3 null(4 rows)

In this example, theORDER BY clause sorts values in thenum column of thesort_demo table in ascending order. It placesNULL after other values.

Note that psql displays null as an empty string by default. To make null clearer, you can execute the following command to change an empty string to another such as null:

\pset null null

Output:

Null displayis "null".

So if you use theASC option, theORDER BY clause uses theNULLS LAST option by default. Therefore, the following query returns the same result:

SELECT  numFROM  sort_demoORDER BY  num NULLS LAST;

Output:

num------    1    2    3 null(4 rows)

To placeNULL before other non-null values, you use theNULLS FIRST option:

SELECT  numFROM  sort_demoORDER BY  numNULLS FIRST;

Output:

num------ null    1    2    3(4 rows)

The following statement sorts values in thenum column of thesort_demo table in descending order:

SELECT  numFROM  sort_demoORDER BY  numDESC;

Output:

num------ null    3    2    1(4 rows)

The output indicates that theORDER BY clause with theDESC option uses theNULLS FIRST by default.

To reverse the order, you can use theNULLS LAST option:

SELECT  numFROM  sort_demoORDER BY  numDESC NULLS LAST;

Output:

num------    3    2    1 null(4 rows)

Summary

• Use theORDER BY clause in theSELECT statement to sort the rows in the query set.
• Use theASC option to sort rows in ascending order andDESC option to sort rows in descending order.
• TheORDER BY clause uses theASC option by default.
• UseNULLS FIRST andNULLS LAST options to explicitly specify the order ofNULL with other non-null values.

Last updated onJanuary 16, 2024

On this page

• Introduction to PostgreSQL ORDER BY clause
• PostgreSQL ORDER BY examples
• PostgreSQL ORDER BY clause and NULL
• Summary