PostgreSQL: Order entries in UNION without using ORDER BY

Introduction

In PostgreSQL, UNION is used to combine the results of two SELECT statements. However, sometimes you might want to order the results in a specific way without using the ORDER BY clause. This tutorial will demonstrate how to achieve that through various techniques.

Implicit Ordering with UNION

Under specific conditions, UNION may result in an implicitly ordered dataset. However, this behavior is not guaranteed and relying on it can lead to unpredictable results. Here’s a simple example:

SELECT 'A' AS letter
UNION
SELECT 'B'
UNION
SELECT 'C';

This may appear ordered, but it’s not safe to assume it will always be the case across different PostgreSQL versions or configurations.

Using Subqueries with Row Numbers

By assigning row numbers to your subqueries, you can control the order of the results. The following example shows how to do this:

SELECT letter FROM (
  SELECT 'A' AS letter, 1 AS order_num
  UNION
  SELECT 'B', 2
  UNION
  SELECT 'C', 3
) AS ordered_union
ORDER BY order_num;

Each SELECT statement within the UNION has an explicit order number you can use for ordering the final result set.

Ordering Using Array Aggregates

Another approach involves using array aggregates:

SELECT unnest(array_agg(letter order by order_num)) FROM (
  SELECT 'A' AS letter, 1 AS order_num
  UNION
  SELECT 'B', 2
  UNION
  SELECT 'C', 3
) AS ordered_union;

This technique aggregates the results into an ordered array, which you then expand using the unnest function to achieve the desired order.

CTE With Explicit Ordering

Common Table Expressions (CTEs) can also help impose order on the UNION result set.

WITH ordered_union AS (
  SELECT 'A' AS letter, 1 AS order_num
  UNION
  SELECT 'B', 2
  UNION
  SELECT 'C', 3
)
SELECT letter FROM ordered_union
ORDER BY order_num;

Here, the CTE defines the order, and the final SELECT statement sorts the result set accordingly.

Concatenating Rows with String Aggregates

String aggregation is another interesting technique to consider:

SELECT string_agg(letter, '') FROM (
  SELECT 'A' AS letter
  UNION ALL
  SELECT 'B'
  UNION ALL
  SELECT 'C'
) AS ordered_union;

Using string_agg with UNION ALL preserves the order and concatenates the entries into a single string.

JSON Functions for Ordered Results

Finally, leveraging PostgreSQL’s JSON capabilities can also provide a solution:

SELECT json_agg(letter order by order_num) FROM (
  SELECT 'A' AS letter, 1 AS order_num
  UNION
  SELECT 'B', 2
  UNION
  SELECT 'C', 3
) AS ordered_union;

Here, json_agg() creates a JSON array that respects the specified order within the subquery.

Conclusion

In this tutorial, we explored several techniques to order the results of a UNION operation without directly using the ORDER BY clause. These methods exploit PostgreSQL’s advanced features and provide reliable ways to control the order of UNION sets for specific use cases.