How do I get the oldest date without using a correlated subquery in SQL?

Question

The following code list all the invoices, and I just want the oldest invoice from a vendor:

SELECT DISTINCT vendor_name, i.invoice_number AS OLDEST_INVOICE, 
    MIN(i.invoice_date), i.invoice_total
FROM vendors v
JOIN invoices i
ON i.vendor_id = v.vendor_id
GROUP BY vendor_name, invoice_number, invoice_total
ORDER BY MIN(i.invoice_date);

Answer 1

We'll use ROW_NUMBER() to "rank" the invoices by date per vendor, and then select only the oldest per vendor:

SELECT vendor_name, invoice_number AS oldest_invoice, invoice_date, invoice_total
  FROM vendors v
 INNER JOIN (SELECT invoices.*,
                    ROW_NUMBER() OVER (PARTITION BY vendor_id ORDER BY invoice_date ASC)
                      AS rn
               FROM invoices) i
       ON i.vendor_id = v.vendor_id
          AND
          i.rn = 1;