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

Question

I have two tables

exchange_rates
TIMESTAMP             curr1    curr2    rate
2018-04-01 00:00:00   EUR      GBP      0.89
2018-04-01 01:30:00   EUR      GBP      0.92
2018-04-01 01:20:00   USD      GBP      1.23

and

transactions
TIMESTAMP             user    curr    amount
2018-04-01 18:00:00   1       EUR     23.12
2018-04-01 14:00:00   1       USD     15.00
2018-04-01 01:00:00   2       EUR     55.00

I want to link these two tables on 1. currency and 2. TIMESTAMP in the following way:

curr in transactions must be equal to curr1 in exchange_rates
TIMESTAMP in exchange_rates must be less than or equal to TIMESTAMP in transactions (so we only pick up the exchange rate that was relevant at the time of transaction)

I have this:

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

but this is linking on two many results as the output is more rows than there are in transactions.

DESIRED OUTPUT:

TIMESTAMP             user    Converted Amount
2018-04-01 18:00:00   1       21.27
2018-04-01 14:00:00   1       18.45
2018-04-01 01:00:00   2       48.95

The logic behind the Converted Amount:

row 1: user spent at 18:00 so take the rate that is less than or equal to the TIMESTAMP in exchange_rates i.e. 0.92 for EUR at 01:30
row 2: user spent at 14:00 so take the rate that is less than or equal to the TIMESTAMP in exchange_rates i.e. 1.23 for USD at 01:20
row 3: user spent at 01:00 so take the rate that is less than or equal to the TIMESTAMP in exchange_rates i.e. 0.89 for EUR at 00:00

How can I do this in postgresql 9.6?

I don't have much time at the moment. So here are some options: **(1)** Use `DISTINCT ON` to reduce your result set. **(2)** Use a subquery in the `SELECT` clause. **(3)** Use a `LATERAL` join. — Thorsten Kettner, Jun 07 '18 at 18:08

score 4 · Accepted Answer · answered Jun 07 '18 at 18:08

You can use a LATERAL JOIN (CROSS APPLY) and limit the result to the first row that match your conditions.

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;

dt                  | usr | conv_amount
:------------------ | --: | ----------:
2018-04-01 18:00:00 |   1 |     21.2704
2018-04-01 14:00:00 |   1 |     18.4500
2018-04-01 01:00:00 |   2 |     48.9500

db<>fiddle here

This would be my preferred way, too. – Thorsten Kettner Jun 07 '18 at 18:11 — Thorsten Kettner, Jun 07 '18 at 18:11

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

1 Answers1