Often in PostgreSQL you may want to order the rows in a table based on the values in a calculated column.
You can use the following syntax to do so:
SELECT *,
points + assists AS total_pa
FROM athletes
ORDER BY total_pa;
This particular example creates a calculated column named total_pa that returns the sum of values in the points and assists columns.
We then use the ORDER BY statement to order the rows of the athletes table based on the values in the calculated column named total_pa.
The following example shows how to use this syntax in practice.
Example: How to Order Rows by Calculated Column in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
athleteID INTEGER,
team TEXT,
points INTEGER,
assists INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 31, 14);
INSERT INTO athletes VALUES (0006, 'Mavs', 16, 3);
INSERT INTO athletes VALUES (0007, 'Hawks', 10, 5);
INSERT INTO athletes VALUES (0008, 'Celtics', 14, 13);
INSERT INTO athletes VALUES (0009, 'Hawks', 20, 8);
INSERT INTO athletes VALUES (0010, 'Hawks', 26, 3);
-- view all rows in table
SELECT * FROM athletes;
athleteid | team | points | assists
-----------+---------+--------+---------
1 | Mavs | 22 | 5
2 | Hawks | 30 | 12
3 | Kings | 12 | 3
4 | Kings | 25 | 9
5 | Mavs | 31 | 14
6 | Mavs | 16 | 3
7 | Hawks | 10 | 5
8 | Celtics | 14 | 13
9 | Hawks | 20 | 8
10 | Hawks | 26 | 3
Suppose that we would like to create a calculated column named total_pa that adds the values from the points and assists columns.
We can use the following syntax to create this calculated column and then order the rows in the table based on the values in this calculated column:
SELECT *,
points + assists AS total_pa
FROM athletes
ORDER BY total_pa;
athleteid | team | points | assists | total_pa
-----------+---------+--------+---------+----------
7 | Hawks | 10 | 5 | 15
3 | Kings | 12 | 3 | 15
6 | Mavs | 16 | 3 | 19
1 | Mavs | 22 | 5 | 27
8 | Celtics | 14 | 13 | 27
9 | Hawks | 20 | 8 | 28
10 | Hawks | 26 | 3 | 29
4 | Kings | 25 | 9 | 34
2 | Hawks | 30 | 12 | 42
5 | Mavs | 31 | 14 | 45
We can see that the resulting table is ordered by the values in the total_pa column in ascending order.
Note that another way to order the rows of a table based on a calculated column is to reference the position of the new calculated column.
For example, we could use ORDER BY 5 since the new total_pa column is in position 5 of the table:
SELECT *,
points + assists AS total_pa
FROM athletes
ORDER BY 5;
athleteid | team | points | assists | total_pa
-----------+---------+--------+---------+----------
7 | Hawks | 10 | 5 | 15
3 | Kings | 12 | 3 | 15
6 | Mavs | 16 | 3 | 19
1 | Mavs | 22 | 5 | 27
8 | Celtics | 14 | 13 | 27
9 | Hawks | 20 | 8 | 28
10 | Hawks | 26 | 3 | 29
4 | Kings | 25 | 9 | 34
2 | Hawks | 30 | 12 | 42
5 | Mavs | 31 | 14 | 45
We can see that this syntax also orders the rows of the table based on the values in the total_pa calculated column.
Feel free to use whichever method you prefer.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Order by Count
PostgreSQL: How to Use ORDER BY with NULL Values Last
PostgreSQL: How to ORDER BY Multiple Values in Specific Order
