Question 371467 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Making a join based on the latest day of the joining table

I have a simple order table in my postgres db and I want to join from a table of payments to create a new column for each order where the latest payment is indicated.
Each payment row looks like this:

id: 1, order_id: 1, status: paid, date: 2024-04-08 16:34:09.860634+02

And if there are more payments to the same order, how can I assign the latest payment (based on date, or id, or simply the latest in the sequence) to the given order row? Some orders might be refunded, therefore I want to take the latest record that has refunded status and append it to the order.

Tags:

2

Answers


  1. 0

    After adjusting the query which I had linked to in the comments, I came up with the following. I have used DB Fiddle to provide a testing ground.

    Table orders – columns and naming conventions are just for testing, you would use your normal table schema and your usual way of naming columns.

    order_id order_buyer_id order_date
    1 12 2024-04-01 12:34:56
    2 1 2024-01-11 22:33:44
    3 2 2024-02-11 20:23:24

    Table payments – like above.

    payment_id payment_order_id payment_status payment_date
    1 1 status 1 2024-04-01 13:52:19
    2 2 status 2 2024-02-02 11:51:19
    3 1 status 3 2024-04-02 11:22:49
    4 2 status 4 2024-02-15 10:41:19
    5 2 status 5 2024-03-22 08:53:36
    6 2 status 6 2024-04-01 15:36:27

    The query below extracts the latest payment, since you mentioned:

    the latest payment (based on date, or id, or simply the latest in the sequence)

    Other ways could be used – you would just target that particular column, instead of the date (which I’m doing in the query).

    SELECT
    tmpDate.order_id,
    tmpDate.order_buyer_id,
    payments.payment_id,
    payments.payment_date,
    payments.payment_status
FROM payments
INNER JOIN (
    SELECT
        MAX(payments.payment_date) AS maxDate,
        orders.order_id,
        orders.order_buyer_id
    FROM payments
    INNER JOIN orders
        ON payments.payment_order_id = orders.order_id
    WHERE payments.payment_date > '0001-01-01 00:00:00'
    AND payments.payment_date IS NOT NULL
    GROUP BY orders.order_id, orders.order_buyer_id
) tmpDate
    ON payments.payment_date = tmpDate.maxDate
    AND payments.payment_order_id = tmpDate.order_id
ORDER BY 
    payments.payment_date DESC,
    payments.payment_order_id ASC

    Output

    Schema (PostgreSQL v10)

    order_id order_buyer_id payment_id payment_date payment_status
    1 12 3 2024-04-02T11:22:49.000Z status 3
    2 1 6 2024-04-01T15:36:27.000Z status 6

    View on DB Fiddle

    Login or Signup to reply.
  2. 0

    You can use DISTINCT ON. For example:

    select distinct on (o.id) o.*, p.payment_date
from ordert o
left join payment p on p.order_id = o.id
order by o.id, p.payment_date desc;

    Result:

     id  buyer_id  order_date           payment_date        
 --- --------- -------------------- ------------------- 
 1   12        2024-04-01 12:34:56  2024-04-02 11:22:49 
 2   1         2024-01-11 22:33:44  2024-04-01 15:36:27 
 3   2         2024-02-11 20:23:24  null

    See running example at db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search