Sling Academy
Home/PostgreSQL/PostgreSQL: Order entries in UNION without using ORDER BY

PostgreSQL: Order entries in UNION without using ORDER BY

Last updated: January 06, 2024

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.

Next Article: PostgreSQL: Using Subqueries with JOINs

Previous Article: PostgreSQL: Using ‘SELECT’ without ‘FROM’

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB