PostgreSQL: How to Use Custom Order for UNION Results

Often in PostgreSQL you may want to specify a custom order for the resulting rows of a UNION between two tables.

You can use the following basic syntax to do so:

SELECT *
FROM (
    SELECT * FROM team1
    UNION ALL
    SELECT * FROM team2
) t
ORDER BY
    CASE
        WHEN role='Starter' THEN 1 
        WHEN role='Bench' THEN 2
        WHEN role='Reserve' THEN 3
        ELSE 4
    END;

This particular example uses the UNION operator to combine the rows from the team1 and team2 tables.

Then, it uses the ORDER BY statement to order the resulting rows in the following order based on values in the role column:

• Starter
• Bench
• Reserve

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

Example: How to Use Custom Order for UNION Results in PostgreSQL

Suppose that we create the following table named team1 that contains information about basketball players on one particular team:

-- create table named 'team1'
CREATE TABLE team1 (
  id INTEGER,
  role TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO team1 VALUES (1, 'Starter', 31);
INSERT INTO team1 VALUES (2, 'Bench', 16);
INSERT INTO team1 VALUES (3, 'Starter', 40);
INSERT INTO team1 VALUES (4, 'Bench', 20);
INSERT INTO team1 VALUES (5, 'Reserve', 12);

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

 id |  role   | points 
----+---------+--------
  1 | Starter |     31
  2 | Bench   |     16
  3 | Starter |     40
  4 | Bench   |     20
  5 | Reserve |     12

Then suppose we create another table named team2 that contains information about basketball players on another team:

-- create table named 'team2'
CREATE TABLE team2 (
  id INTEGER,
  role TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO team2 VALUES (1, 'Reserve', 6);
INSERT INTO team2 VALUES (2, 'Starter', 22);
INSERT INTO team2 VALUES (3, 'Starter', 25);
INSERT INTO team2 VALUES (4, 'Bench', 12);
INSERT INTO team2 VALUES (5, 'Reserve', 4);

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

 id |  role   | points 
----+---------+--------
  1 | Reserve |      6
  2 | Starter |     22
  3 | Starter |     25
  4 | Bench   |     12
  5 | Reserve |      4

Suppose that we would like to combine these two tables into one table and order the rows in the following order based on values in the role column:

• Starter
• Bench
• Reserve

We can use the following syntax to do so:

SELECT *
FROM (
    SELECT * FROM team1
    UNION ALL
    SELECT * FROM team2
) t
ORDER BY
    CASE
        WHEN role='Starter' THEN 1 
        WHEN role='Bench' THEN 2
        WHEN role='Reserve' THEN 3
        ELSE 4
    END;

 id |  role   | points 
----+---------+--------
  1 | Starter |     31
  3 | Starter |     40
  2 | Starter |     22
  3 | Starter |     25
  4 | Bench   |     20
  4 | Bench   |     12
  2 | Bench   |     16
  5 | Reserve |      4
  5 | Reserve |     12
  1 | Reserve |      6

The resulting table contains all rows from both tables, ordered in the exact manner that we specified.

You can use similar syntax to order the resulting rows of a UNION operator with your own tables in PostgreSQL.

Additional Resources

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

PostgreSQL: How to Use LEFT JOIN on Multiple Tables
PostgreSQL: How to Use INNER JOIN with GROUP BY
PostgreSQL: How to Perform an Anti-Join