General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
How to Join with Top 1 Select?
Environment versions
Network:
Windows 2003 Server
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.
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only