The Art of PostgreSQL/

...

/

Ordering with the Order By

Ordering with the Order By

Learn about the order by clause, kNN ordering, and GiST indexes.

We'll cover the following...

The SQL order by clause is pretty well-known because SQL doesn’t guarantee any ordering of the result set of any query except when we use the order by clause.

In its simplest form, the order by works with one column or several columns that are part of our data model. In some cases, it might even allow PostgreSQL to return the data in the right order by following an existing index.

Press + to interact
  select year, url
    from seasons
order by year desc
   limit 3;

This gives an expected and not that interesting result set:

 year │                          url                          
══════╪═══════════════════════════════════════════════════════
 2017 │ https://en.wikipedia.org/wiki/2017_Formula_One_season
 2016 │ https://en.wikipedia.org/wiki/2016_Formula_One_season
 2015 │ http://en.wikipedia.org/wiki/2015_Formula_One_season

What is more interesting about this is the explain plan of the query, where we see PostgreSQL follows the primary key index of the table in a backward direction in order to return our three most recent entries. We obtain the plan ...