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:

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.