PostgreSQL Basics: Exploring Order By

In this tutorial, we will be exploring Order By and how to use them for sorting results returned by Select statement.

Introduction

As, we know when we run Selectstatement. It returns some data and that data is in unsorted form. Hence, Order By is used to sort data returned by Selectstatement in ascending or descending order based on the expression provided.

Basic Syntax

SELECT select_list
FROM table_name
ORDER BY sort_expression1 [ASC | DESC], sort_expression2 [ASC | DESC], ......, sort_expressionN [ASC | DESC]

Here, we can provide multiple sort_expression separated by , to sort our data. It sorts based on the sequence. First according to first expression then second and so on.
We can use either ASC for ascending or DESC for descending.

Note: ASC is by default. Hence, if you use nothing it will sort data in ascending order.

Following is the flow of the PostgreSQL statements during execution.

Hence, because of this order. It is possible to use column alias defined in Select clause in Order BY clause.

Examples

Assume we have customer table and we run following query.

Code

SELECT first_name, last_name
FROM customer
ORDER BY first_name ASC;

Output

As, we discussed earlier that ASC is default. Hence, we could write our query as following too.

Code

SELECT first_name, last_name
FROM customer
ORDER BY first_name;

It will generate same results.

Now, let's sort in descending order.

Code

SELECT first_name, last_name
FROM customer
ORDER BY last_name DESC;

Output

Let's sort using multiple column!

Code

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

Output

As you can observe, it first sorts based on first name in ascending order then it sorts based on last name in descending order.