Sorting (ORDER BY)

This is key word that allow you to sort the results of the SELECT.

In the next cell, I create everything I need for the examples in this page.

%%bash
# creating a container and comming to psql command line
docker run -d --rm\
    --name order_by_examples\
    -e POSTGRES_PASSWORD=docker_app\
    -v ./load_tables/CSV/iris_csv.csv:/iris_csv.csv\
    postgres:15.4 &> /dev/null
sleep 5
docker exec -i order_by_examples psql -U postgres -d postgres

CREATE TABLE table_for_ordering(
    int_value INT,
    text_value TEXT
);
INSERT INTO table_for_ordering(int_value, text_value) VALUES
(4, 'ab'),
(1, 'bw'),
(1, 'bc'),
(3, 'ba'),
(5, 'cg'),
(2, 'cd'),
(7, 'ba');

CREATE TABLE
INSERT 0 7

Here is the table used for the examples. As you can see, I’ve put some variables in there and not sorted them all in any systematic way.

%%bash
docker exec -i order_by_examples psql -U postgres -d postgres
SELECT * FROM table_for_ordering;

 int_value | text_value 
-----------+------------
         4 | ab
         1 | bw
         1 | bc
         3 | ba
         5 | cg
         2 | cd
(6 rows)

Note don’t forget to stop the container when you finish playing with examples.

!docker stop order_by_examples &> /dev/null

Numeric variable

Very simple - just sort records in ascending order for the selected variable.

%%bash
docker exec -i order_by_examples psql -U postgres -d postgres

SELECT * FROM table_for_ordering ORDER BY int_value;

 int_value | text_value 
-----------+------------
         1 | bw
         1 | bc
         2 | cd
         3 | ba
         4 | ab
         5 | cg
(6 rows)

Text varible

It seems that sorting by text variables is done in the usual order according to the encoding table, аnd if the first i characters are the same, the decision will be made based on the i+1th character. The following example confirms it.

%%bash
docker exec -i order_by_examples psql -U postgres -d postgres

SELECT * FROM table_for_ordering ORDER BY text_value;

 int_value | text_value 
-----------+------------
         4 | ab
         3 | ba
         1 | bc
         1 | bw
         2 | cd
         5 | cg
(6 rows)

Descending

Dy default ORDER BY sort values in ascending order, but by using key word DESC after column name in ORDER BY block you can make posgres use descending order.

So in following example I apply this option for int_value.

%%bash
docker exec -i order_by_examples psql -U postgres -d postgres

SELECT * FROM table_for_ordering ORDER BY int_value DESC;

 int_value | text_value 
-----------+------------
         7 | ba
         5 | cg
         4 | ab
         3 | ba
         2 | cd
         1 | bc
         1 | bw
(7 rows)

Order by several

You can use multiple columns in the ORDER BY block. In this case the records will be sorted by the ith column, but if there are some options caused by equal values in the ith column, the i+1th column will be used to determine the final order.

So in the following example:

• The first query uses int_value, text_value in ORDER BY;

  • It has int_value = 1 twice, but the final order is determined by 'bc'<'dw' in text_value;

• Second query uses text_value, int_value DESC in ORDER BY.

  • It has text_value = 'ba' twice, but I also mentioned sorting by int_value in descending order, so 7 | ba is preferable to 3 | ba.

%%bash
docker exec -i order_by_examples psql -U postgres -d postgres

SELECT * FROM table_for_ordering ORDER BY int_value, text_value;
SELECT * FROM table_for_ordering ORDER BY text_value, int_value DESC;

 int_value | text_value 
-----------+------------
         1 | bc
         1 | bw
         2 | cd
         3 | ba
         4 | ab
         5 | cg
         7 | ba
(7 rows)

 int_value | text_value 
-----------+------------
         4 | ab
         7 | ba
         3 | ba
         1 | bc
         1 | bw
         2 | cd
         5 | cg
(7 rows)