• Dev Rel
  • Technical
  • PostgreSQL
PostgreSQL® ORDER BY

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:

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 

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 

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 

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 

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

Result

best_player 
Lynnea 

Start a free trial for managed PostgreSQL

Get Started