I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work:
SELECT Max(NumInvoicesState)
FROM
(
SELECT VendorState,VendorName,
Count(InvoiceID) AS NumInvoicesState
FROM VENDORS V
JOIN Invoices I
on V.VendorID=I.VendorID
GROUP by VendorState, VendorName
) AS Alias
My idea is to first do a query to get a table with a list of vendors by state and the number of invoices for each vendor.
I thought of joining the inner-table to Vendors and then group by VendorState, but that does not seem to work.
I guess I could SELECT VendorName, VendorState then join it with Vendors on, say VendorState and then Group by Vendors. But then I get all the Vendors, not just the ones with the max.