Share this page: Follow me to be notified of new articles:

ORDER BY [my custom order] in PostgreSQL

Introduction

Below you will find a little tutorial on how to sort data in a custom order.
For example, if you have a list of team members, you may want to sort them in a specific order (CEO first, then the COO...) instead of an alphabetical/numeric order :
  name   | function |  phone_number  
---------+----------+----------------
 Gabriel | CEO      | (215) 267-0000
 Jerome  | COO      | (215) 267-4444
 Bob     | CFO      | (215) 267-3333
 Bill    | CTO      | (215) 267-2222
 Tom     | CIO      | (215) 267-1111
Theses veggies were sorted in a custom order
Theses veggies were sorted in a custom order

Try it yourself

If you'd like to try the above example for yourself, just create this table:

Create a custom sorting function

In order to sort by a custom list, you can use the function below:
gab@test> CREATE OR REPLACE FUNCTION custom_sort(anyarray, anyelement)
  RETURNS INT AS 
$$
  SELECT i FROM (
     SELECT generate_series(array_lower($1,1),array_upper($1,1))
  ) g(i)
  WHERE $1[i] = $2
  LIMIT 1;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION
Time: 57.775 ms

Thanks to the PostgreSQL wiki!

Use the function for the custom sort

A regular select will not sort the data:
gab@test> SELECT name, function, phone_number FROM team;

  name   | function |  phone_number  
---------+----------+----------------
 Tom     | CIO      | (215) 267-1111
 Gabriel | CEO      | (215) 267-0000
 Bill    | CTO      | (215) 267-2222
 Bob     | CFO      | (215) 267-3333
 Jerome  | COO      | (215) 267-4444
(5 rows)

You can custom the "order by" with this simple query using the function above:
gab@test> SELECT name, function, phone_number FROM team
ORDER BY custom_sort (ARRAY['CEO', 'COO', 'CFO', 'CTO', 'CIO'], team.function);

  name   | function |  phone_number  
---------+----------+----------------
 Gabriel | CEO      | (215) 267-0000
 Jerome  | COO      | (215) 267-4444
 Bob     | CFO      | (215) 267-3333
 Bill    | CTO      | (215) 267-2222
 Tom     | CIO      | (215) 267-1111
(5 rows)

Other solutions

If you are not comfortable using a function, some other solutions are possible, like using a "CASE" clause:
gab@test> SELECT name, function, phone_number FROM team
ORDER BY CASE WHEN function = 'CEO' THEN 1
              WHEN function = 'COO' THEN 2
              WHEN function = 'CFO' THEN 3
              WHEN function = 'CTO' THEN 4
              WHEN function = 'CIO' THEN 5
         END;

  name   | function |  phone_number  
---------+----------+----------------
 Gabriel | CEO      | (215) 267-0000
 Jerome  | COO      | (215) 267-4444
 Bob     | CFO      | (215) 267-3333
 Bill    | CTO      | (215) 267-2222
 Tom     | CIO      | (215) 267-1111
(5 rows)

Another solution is to use a JOIN table:
gab@test> SELECT name, function, phone_number
FROM team
JOIN (VALUES ('CEO', 1), ('COO' ,2), ('CFO', 3), ('CTO', 4), ('CIO', 4)) as x(value, order_number) ON team.function = x.value
ORDER BY x.order_number;

  name   | function |  phone_number  
---------+----------+----------------
 Gabriel | CEO      | (215) 267-0000
 Jerome  | COO      | (215) 267-4444
 Bob     | CFO      | (215) 267-3333
 Tom     | CIO      | (215) 267-1111
 Bill    | CTO      | (215) 267-2222
(5 rows)
Last update of this page: March 27, 2017
Please do not copy any of the content of this website without asking me first.