Fornecedores com mais faturas no estado

Eu acho que a consulta interna está certa.

O MAX sem um GROUP BY correspondente na consulta externa fornecerá um único valor de todo o conjunto de dados. No seu exemplo, o conjunto de dados seria a consulta interna. Isso não é o que você deseja, então você deve adicionar group by state.

Agora você tem o problema do nome do fornecedor. Não pode estar na lista de seleção, a menos que também esteja no GROUP BY, mas isso dará o resultado errado. É necessário fazer isso em duas etapas. Na consulta externa, apenas lide com os estados:

Select state, max (..) as MaxByState
From (inner query)
Group by state

Em seguida, junte isso de volta à consulta interna no valor MAX() :

Select
  ...
from (
    Select state, max (..) as MaxByState
    From (inner query)
    Group by state
) as mbs
inner join (
  Inner query
) as iq
on mbs.state = iq.state
and mbs.MaxByState = iq.NumInvoicesState;

Ou seja, aqui estão as faturas mais numerosas de cada estado. Quais fornecedores têm tantas faturas?

Se dois fornecedores tiverem o mesmo número de faturas em um estado, haverá duas linhas de saída.

Como a consulta interna é repetida literalmente, seria melhor apresentada como um CTE não recursivo .

O SQL Server 2012 possui várias funções de janela integradas. A mais útil aqui é RANK () . Ele faz exatamente o que você acha que deveria fazer.

Select 
  RANK ( ) OVER (PARTITION BY state order by NumInvoicesState )
from (inner query) as a

Isso elimina todo o alinhamento do conjunto de dados de segunda ordem em valores agregados derivados exigidos acima. É muito mais fácil de ler e entender e, portanto, de manter.

WITH Counts AS (
    SELECT  VendorState, 
            VendorName,
            Count(InvoiceID) AS NumInvoicesState
    FROM    Vendors v
    JOIN    Invoice i 
    ON      v.VendorID = i.VendorID 
    GROUP BY VendorState, VendorName
    ), Ranks As (
    SELECT  VendorState,
            VendorName,
            NumInvoicesState,
            ROW_NUMBER() OVER (PARTITION BY VendorState ORDER BY NumInvoicesState DESC) As StateRank
    FROM    Counts
    )
SELECT  VendorState,
        VendorName,
        NumInvoicesState
FROM    Ranks
WHERE   StateRank = 1
ORDER BY 3, 1;

Observação: Se houver mais de um fornecedor com o mesmo número de faturas, ele escolherá um de forma arbitrária. Também pode haver uma maneira mais compacta de fazer isso.

Em relação ao seu pedido para criticar sua tentativa ... o seu não funciona porque você está retornando apenas um número máximo e é isso. Você disse que queria que o fornecedor com mais faturas fosse dividido por estado. Isso exigirá algumas etapas e WITH é uma maneira simples de dividir uma consulta complexa em subconsultas que fazem referência umas às outras.

A primeira parte do WITH é exatamente a sua consulta (apenas reformatada para ficar mais clara). Ele obtém os dados básicos... os estados, os fornecedores e quantas faturas eles tinham.

A segunda parte da instrução faz referência à primeira tabela e apenas adiciona números de linha. Eles começam em 1 para cada estado e são ordenados para baixo, dependendo da contagem da fatura. Isso é separado porque precisamos dessa primeira consulta para obter esses dados para nós antes de começarmos a trabalhar neles.

A parte final da instrução é apenas retornar a linha superior de cada estado, obtendo onde quer que o número da linha seja 1, ou seja, onde quer que seja a linha superior de cada estado.

Existem maneiras prováveis ​​de tornar isso mais compacto ou eficiente. Mas, para fins de ensino e legibilidade, que incluem sua consulta principal, é assim que é feito.

Conforme sugerido por Cody Konior, você precisa encontrar a classificação de cada fornecedor em uma partição de estado por estado do fornecedor e, em seguida, encontrar todos os registros nos quais as classificações foram 1. Tentarei conectar a solução dele à sua tentativa:

SELECT t2.VendorState [State], t2.VendorName VendorWithMostSales
FROM (
SELECT VendorState,
VendorName,
NumInvoicesState,
ROW_NUMBER() OVER (PARTITION BY VendorState ORDER BY NumInvoicesState DESC) As StateRank
FROM 
 (
   SELECT  VendorState,VendorName,
      Count(InvoiceID) AS NumInvoicesState 
   FROM VENDORS V 
   JOIN Invoices I
     on V.VendorID=I.VendorID
   GROUP by  VendorState, VendorName
 ) t1
) t2
WHERE t2.StateRank = 1;

Se você quiser dois nomes em vez de um em caso de empate nas vendas, use DENSE_RANKou RANK. Para dados com um empate entre a segunda e a terceira posição ROW_NUMBERvai como 1,2,3,4,5... RANKvai como 1,2,2,4,5... e DENSE_RANKvai como 1,2,2,3,4 ...

Uma revisão tardia para uma entrevista:

SELECT TOP 1 Count(InvoiceID) Most , VendorState
FROM Vendors V JOIN Invoices I
 ON V.VendorID=I.VendorID
 GROUP BY VendorState
 ORDER BY Most DESC

Correu com a resposta certa. Obrigado a todos por sua entrada.

Um semelhante: O estado com mais fornecedores:

SELECT TOP 1 Count(VendorState) MostV , VendorState
FROM Vendors V JOIN Invoices I
ON V.VendorID=I.VendorID
GROUP BY VendorState
ORDER BY MostV DESC