# PostgreSQL® Full-Text Search

[Blog](/blog/)&gt;[Technology](/blog/category/technical/)&gt;PostgreSQL® ORDER BY 

PostgreSQL® ORDER BY
====================

January 31, 2023 | By [ John Del Castillo](https://instaclustr.husldigital.com/blog/author/john-delcastilloinstaclustr-com/)

 

 

 

 



   [ ](https://x.com/intent/tweet?text=PostgreSQL%C2%AE%20ORDER%20BY&url=https://instaclustr.husldigital.com/blog/postgresql-order-by/) [ ](https://www.linkedin.com/shareArticle?mini=true&url=https://instaclustr.husldigital.com/blog/postgresql-order-by/&title=&summary=PostgreSQL%C2%AE%20ORDER%20BY&source=) 

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](https://www.postgresql.org/docs/14/sql-select.html) statement in PostgreSQL, the resulting rows are returned in an unspecified order. To have these results sorted, we need to use the [Order By](https://www.postgresql.org/docs/current/queries-order.html) clause.

The basic syntax of an SELECT statement using Order By is as follows:































SELECT select\_list FROM table\_expression ORDER BY sort\_expression1 \[ASC | DESC\] \[NULLS { FIRST | LAST }\] \[, sort\_expression2 \[ASC | DESC\] \[NULLS { FIRST | LAST }\] ...\]

   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 412Olvan 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 































SELECT player, level, score FROM highscores ORDER BY score DESC

   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 































SELECT player, level, score, time\_sec FROM highscores ORDER BY score DESC, time\_sec

   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































INSERT into highscores VALUES ('baddata', null, null, null);

   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































SELECT player, level, score FROM highscores ORDER BY score DESC nulls last

   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































SELECT player as best\_player FROM highscores ORDER BY score/time\_sec DESC nulls last LIMIT 1;

   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 Start a free trial for managed PostgreSQL

[Get Started ](https://console2.instaclustr.com/signup?_ga=2.219042333.545870593.1680584441-1640350992.1644797276)

 

 

 

 [ Add Instaclustr as a preferred source on Google ](https://google.com/preferences/source?q=instaclustr.com)



 

 ![mail icon]()#### Get the latest articles for open sourceIn your inbox

 <a class="btn btn-primary btn-popup text-dark" href="">Sign up now</a> 

 

 

 

  ### Related content

 [ 6 Reasons to Migrate From Oracle to PostgreSQL® 

 

 PostgreSQL is a powerful and versatile database management system if you’re considering migrating from Oracle to PostgreSQL... 

 

 

 

 

 

 

 ](https://instaclustr.husldigital.com/blog/oracle-to-postgresql/) 

 [ Top 12 PostgreSQL® data types and how data types work in PgJDBC 

 

 Discover what data types are available in PostgreSQL and determined the definitive mapping from PostgreSQL to SQL/JDBC to Java ... 

 

 

 

 

 

 

 ](https://instaclustr.husldigital.com/blog/postgresql-data-types-mappings-to-sql-jdbc-and-java-data-types/) 

 [ PostgreSQL® Full-Text Search 

 

 Discover the capabilities of PostgreSQL and enhance your search functionality. Read our guide on mastering the full-text and ... 

 

 

 

 

 

 

 ](https://instaclustr.husldigital.com/blog/postgresql-full-text-search/) 

 

  <a class="close-modal" href="">×</a>Sign upto ourNewsletter
-----------------------
