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

Try it yourself

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

CREATE TEMP TABLE team (
   id serial,
   name text,
   function text,
   phone_number text
);

INSERT INTO team (name, function, phone_number)
VALUES ('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');

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)