PostgreSQL: ORDER BY Multiple Values in Specific Order

Often in PostgreSQL you may want to use the ORDER BY statement to order the rows of a table based on multiple values in a specific order.

You can use the following syntax to do so:

SELECT *
FROM athletes
ORDER BY array_position(ARRAY['Forward', 'Guard', 'Center']::varchar[], pos)

This particular example orders the rows of the athletes table based on the following specific order of values from the pos column:

• Forward
• Guard
• Center

The following example shows how to use this syntax in practice.

Example: How to ORDER BY Multiple Values in Specific Order in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  team TEXT,
  pos TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Forward', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 'Center', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 'Guard', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 'Forward', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 'Center', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 'Guard', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 'Forward', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 'Forward', 33);

-- view all rows in table
SELECT * FROM athletes;

 id | team |   pos   | points 
----+------+---------+--------
  1 | Mavs | Guard   |     22
  2 | Mavs | Forward |     30
  3 | Mavs | Guard   |     12
  4 | Mavs | Center  |      9
  5 | Mavs | Guard   |     14
  6 | Heat | Forward |     16
  7 | Heat | Center  |     40
  8 | Heat | Guard   |     18
  9 | Heat | Forward |     16
 10 | Heat | Forward |     33

Suppose that we would like to order the rows of the athletes table based on the following specific order of values from the pos column: Forward, Guard, Center.

We can use the following syntax to do so:

SELECT *
FROM athletes
ORDER BY array_position(ARRAY['Forward', 'Guard', 'Center']::varchar[], pos)

 id | team |   pos   | points 
----+------+---------+--------
 10 | Heat | Forward |     33
  2 | Mavs | Forward |     30
  6 | Heat | Forward |     16
  9 | Heat | Forward |     16
  5 | Mavs | Guard   |     14
  1 | Mavs | Guard   |     22
  8 | Heat | Guard   |     18
  3 | Mavs | Guard   |     12
  7 | Heat | Center  |     40
  4 | Mavs | Center  |      9

Notice that the rows of the resulting table are sorted in the exact order that we specified.

Feel free to use similar syntax to sort the rows of your own table in a custom specific order.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Count Occurrences of Each Value in Column
PostgreSQL: How to Count Number of Occurrences of Character in String
PostgreSQL: How to Count True and False Values