In this article we are going to learn how to use the ORDER BY clause in PostgreSQL® to sort the results from SELECT queries.
Sorting Rows
When we make a SELECT statement in PostgreSQL, the resulting rows are returned in an unspecified order. To have these results sorted, we need to use the Order By clause.
The basic syntax of an SELECT statement using Order By is as follows:
|
1 2 3 4 |
SELECT select_list FROM table_expression ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }] [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...] |
PostgreSQL Order By example data
We’ll use this HIGHSCORES table for our PostgreSQL Order By examples.
| Player | Level | Score | Time_Sec |
| Gizela | 2 | 2,000 | 169 |
| Amye | 5 | 12,000 | 595 |
| Alonso | 6 | 12,000 | 581 |
| Rolph | 2 | 2,000 | 169 |
| Costanza | 2 | 3,000 | 194 |
| Wadsworth | 1 | 2,000 | 109 |
| Felice | 1 | 2,000 | 109 |
| Lynnea | 6 | 18,000 | 802 |
| Wendeline | 4 | 7,000 | 412 |
| Olvan | 3 | 8,000 | 377 |
| Gunther | 6 | 12,000 | 655 |
PostgreSQL Order By a single column
To sort the table by the highest score to lowest:
Query
|
1 2 3 |
SELECT player, level, score FROM highscores ORDER BY score DESC |
Result
| Player | Level | Score |
| Lynnea | 6 | 18,000 |
| Amye | 5 | 12,000 |
| Alonso | 6 | 12,000 |
| Gunther | 6 | 12,000 |
| Gizela | 5 | 11,000 |
| Olvan | 3 | 8,000 |
| Wendeline | 4 | 7,000 |
| Costanza | 2 | 3,000 |
| Felice | 1 | 2,000 |
| Wadsworth | 1 | 2,000 |
| Rolph | 2 | 2,000 |
PostgreSQL Order By two columns
To sort the table by score: highest to lowest; then by time taken: lowest to highest
Query
|
1 2 3 |
SELECT player, level, score, time_sec FROM highscores ORDER BY score DESC, time_sec |
Result
| Player | Level | Score | Time_Sec |
| Lynnea | 6 | 18,000 | 802 |
| Alonso | 6 | 12,000 | 581 |
| Amye | 5 | 12,000 | 595 |
| Gunther | 6 | 12,000 | 655 |
| Gizela | 5 | 11,000 | 570 |
| Olvan | 3 | 8,000 | 377 |
| Wendeline | 4 | 7,000 | 412 |
| Costanza | 2 | 3,000 | 194 |
| Felice | 1 | 2,000 | 109 |
| Wadsworth | 1 | 2,000 | 109 |
| Rolph | 2 | 2,000 | 169 |
PostgreSQL Order By with NULL values
Let’s insert a new value with incomplete data
|
1 2 |
INSERT into highscores VALUES ('baddata', null, null, null); |
Now if we execute the query from Example 1, we get the following results:
| Player | Level | Score |
| baddata | null | null |
| Lynnea | 6 | 18,000 |
| Amye | 5 | 12,000 |
| Alonso | 6 | 12,000 |
| Gunther | 6 | 12,000 |
| Gizela | 5 | 11,000 |
| Olvan | 3 | 8,000 |
| Wendeline | 4 | 7,000 |
| Costanza | 2 | 3,000 |
| Rolph | 2 | 2,000 |
| Felice | 1 | 2,000 |
| Wadsworth | 1 | 2,000 |
We can move the null values to the end by executing this query
|
1 2 3 |
SELECT player, level, score FROM highscores ORDER BY score DESC nulls last |
Result
| Player | Level | Score |
| Lynnea | 6 | 18,000 |
| Amye | 5 | 12,000 |
| Alonso | 6 | 12,000 |
| Gunther | 6 | 12,000 |
| Gizela | 5 | 11,000 |
| Olvan | 3 | 8,000 |
| Wendeline | 4 | 7,000 |
| Costanza | 2 | 3,000 |
| Wadsworth | 1 | 2,000 |
| Felice | 1 | 2,000 |
| Rolph | 2 | 2,000 |
| baddata | null | null |
PostgreSQL Order By an expression
It’s possible to use Order By with an arbitrary expression.
In this query, we will find the overall best player. We calculate this by dividing the score by time_sec in descending order and then returning the top result.
Query
|
1 2 3 4 |
SELECT player as best_player FROM highscores ORDER BY score/time_sec DESC nulls last LIMIT 1; |
Result
| best_player |
| Lynnea |