I have the tables:
- Invoices ( VendorID, InvoiceDate, InvoiceNumber, InvoiceTotal,...),
- Vendors (VendorName, VendorID,....)
I am trying to obtain a list of the earliest invoices for each vendor, together with VendorName
, InvoiceNumber
, InvoiceDate
, InvoiceTotal
(for that earliest date).
Here is what I have :
SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Vendors V JOIN Invoices I ON V.VendorID=I.VendorID
WHERE InvoiceDate <= ( SELECT Min(InvoiceDate)
FROM Invoices
JOIN Vendors ON V.VendorID=Vendors.VendorID )
GROUP BY VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal
The problem is that I am getting only one invoice, which is the earliest of all invoices, not the earliest by state. What is going on?