Get the results of a subquery in SQL

Question

How do you create a join to get the latest invoice for all customers?

Tables:
- Invoices
- Customers

Customers table has: id, last_invoice_sent_at, last_invoice_guid
Invoices table has: id, customer_id, sent_at, guid

I'd like to fetch the latest invoice for every customer and, with that data, update last_invoice_sent_at and last_invoice_guid in the Customers table.

Answer 1

You want to use distinct on. For a query soring by customer_id and then by invoice, it would return the first row for each distinct value indicated in distinct on. That is the rows with * below:

customer_id | sent_at     |
1           | 2014-07-12  | * 
1           | 2014-07-10  | 
1           | 2014-07-09  |
2           | 2014-07-11  | *
2           | 2014-07-10  |

So your update query could look like:

update customers
set last_invoice_sent_at = sent_at
from (
  select distinct on (customer_id)
    customer_id,
    sent_at
  from invoices
  order by customer_id, sent_at desc
) sub
where sub.customer_id = customers.customer_id

Answer 2

@Konrad provided a flawless SQL statement. But since we are only interested in a single column, GROUP BY will be more efficient than DISTINCT ON (which is great to retrieve multiple columns from the same row):

UPDATE customers c
SET    last_invoice_sent_at = sub.last_sent
FROM  (
   SELECT customer_id, max(sent_at) AS last_sent
   FROM   invoices
   GROUP  BY 1
   ) sub
WHERE sub.customer_id = c.customer_id;