How to link two tables but only take the MAX value from one table in PostgreSQL?

Introduction

When working with multiple tables, it’s often necessary to join them together based on common columns. However, when these columns also have a natural ordering (like timestamps), we might want to only consider the most recent or relevant row from one of those tables for our calculations.

In this blog post, we’ll explore how to link two tables in PostgreSQL and only take the max value from one table where the other table has at least one match based on both common columns.

Background

PostgreSQL is a powerful object-relational database that supports various advanced data modeling techniques. In this specific case, we’re dealing with cross-joining two tables using lateral joins (also known as cross apply in some databases).

A lateral join allows us to reference the rows of one table relative to each row of another table, which can be particularly useful when working with derived tables or subqueries.

Our Initial Problem

Let’s look at our initial query that’s trying to link transactions and exchange_rates based on common columns:

SELECT
trans.TIMESTAMP, trans.user,
-- Multiply the amount in transactions by the corresponding rate in exchange_rates
trans.amount * er.rate AS "Converted Amount"
FROM transactions trans, exchange_rates er
WHERE trans.curr = er.curr1
AND er.TIMESTAMP <= trans.TIMESTAMP
ORDER BY trans.user

This query attempts to perform an inner join on transactions and exchange_rates based on the curr column in transactions matching curr1 in exchange_rates, as well as ensuring that the timestamp in exchange_rates is less than or equal to the one in transactions. However, this results in more rows than there are in transactions.

The Desired Output

We want a result set where each row from transactions is associated with its most recent exchange rate in exchange_rates, based on both curr1 and timestamp. This is essentially an anti-join, where we only select rows that match our conditions.

The expected output for the given example data would be:

The Solution

To achieve this, we can use a lateral join in PostgreSQL to cross-reference the rows of exchange_rates relative to each row in transactions. We’ll limit our result to only include the most recent match for each transaction.

Here’s how you could write this query:

SELECT t.dt, t.usr, t.amount * e.rate AS conv_amount
FROM   transactions t
JOIN LATERAL (
  SELECT *
  FROM exchange_rates er
  WHERE t.curr = er.curr1
  AND er.dt <= t.dt
  ORDER BY dt DESC
  LIMIT 1
) e ON TRUE;

This query will return the desired output, where each row from transactions is associated with its most recent exchange rate in exchange_rates.

Conclusion

When working with data models that include multiple tables and timestamps, lateral joins can be a powerful tool for cross-referencing rows based on both columns. By using these techniques, we can create more robust and efficient queries that produce the desired results.

Whether you’re dealing with common use cases or more complex scenarios, understanding how to work with lateral joins in PostgreSQL can help you unlock more insightful and data-driven insights from your databases.

Do you have any other questions about this topic?

Last modified on 2025-04-07