# ORDER BY [my custom order] in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

### 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

### 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)
``````