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.