I'm creating a local view where I need only the top result from one of the tables in the view, but all the records in another. IE: All Customers, but only the most recent Invoice for each.

SELECT cust.CustID, cust.LastName, cust.FirstName, Invoice.InvoiceID, Invoice.InvoiceNum, Invoice.Date FROM cust INNER JOIN invoice ON cust.CustID = invoice.cust ID (TOP 1 IN invoice????)

Because it's a local view, I can't gracefully do it with a subquery, and I'd prefer to avoid SCANning through customer and inserting the results. A GROUP BY doesn't seem to work because I need the actual values from invoice, so I'm stuck.