SQL Server: Como ingressar na primeira linha

Vou usar um exemplo concreto, mas hipotético.

Cada pedido normalmente tem apenas um item de linha :

Encomendas:

OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A 

LineItems:

 LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing 

Mas ocasionalmente haverá um pedido com dois itens de linha:

 LineItemID Order ID Quantity Description ========== ======== ======== ================================= {A58A1...} 6,784,329 5 pentametric fan {0E9BC...} 6,784,329 5 differential girdlespring 

Normalmente, ao mostrar os pedidos para o usuário:

 SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID 

Eu quero mostrar o item único no pedido. Mas com essa ordem ocasional contendo dois (ou mais) itens, os pedidos apareceriam duplicados :

 OrderNumber Quantity Description =========== ======== ==================== STL-7442-1 7 prefabulated amulite MPT-9931-8A 32 spurving bearing KSG-0619-81 5 panametric fan KSG-0619-81 5 differential girdlespring 

O que eu realmente quero é ter o SQL Server apenas escolher um , pois será bom o suficiente :

 OrderNumber Quantity Description =========== ======== ==================== STL-7442-1 7 prefabulated amulite MPT-9931-8A 32 differential girdlespring KSG-0619-81 5 panametric fan 

Se eu for aventureiro, poderei mostrar ao usuário, uma elipse para indicar que há mais de um:

 OrderNumber Quantity Description =========== ======== ==================== STL-7442-1 7 prefabulated amulite MPT-9931-8A 32 differential girdlespring KSG-0619-81 5 panametric fan, ... 

Então a questão é como quer

  • eliminar linhas “duplicadas”
  • somente junte-se a uma das linhas, para evitar duplicação

Primeira tentativa

Minha primeira tentativa ingênua foi unir-se aos itens de linha ” TOP 1 “:

 SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders INNER JOIN ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE LineItems.OrderID = Orders.OrderID) LineItems2 ON 1=1 

Mas isso dá o erro:

A coluna ou prefixo ‘Orders’ não corresponde a um nome de tabela ou nome de alias usado na consulta.

Presumivelmente porque a seleção interna não vê a mesa externa.

   
 SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders JOIN LineItems ON LineItems.LineItemGUID = ( SELECT TOP 1 LineItemGUID FROM LineItems WHERE OrderID = Orders.OrderID ) 

No SQL Server 2005 e acima, você poderia simplesmente replace INNER JOIN por CROSS APPLY :

 SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description FROM Orders CROSS APPLY ( SELECT TOP 1 LineItems.Quantity, LineItems.Description FROM LineItems WHERE LineItems.OrderID = Orders.OrderID ) LineItems2 

Eu sei que esta questão foi respondida há algum tempo, mas quando se lida com grandes conjuntos de dados, as consultas aninhadas podem ser caras. Aqui está uma solução diferente em que a consulta aninhada só será executada uma vez, em vez de para cada linha retornada.

 SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders INNER JOIN ( SELECT Orders.OrderNumber, Max(LineItem.LineItemID) AS LineItemID FROM Orders INNER JOIN LineItems ON Orders.OrderNumber = LineItems.OrderNumber GROUP BY Orders.OrderNumber ) AS Items ON Orders.OrderNumber = Items.OrderNumber INNER JOIN LineItems ON Items.LineItemID = LineItems.LineItemID 

Você poderia fazer:

 SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID WHERE LineItems.LineItemID = ( SELECT MIN(LineItemID) FROM LineItems WHERE OrderID = Orders.OrderID ) 

Isso requer um índice (ou chave primária) em LineItems.LineItemID e um índice em LineItems.OrderID ou será lento.

@Quassnoi resposta é boa, em alguns casos (especialmente se a tabela externa é grande), uma consulta mais eficiente pode ser com o uso de funções em janelas, como este:

 SELECT Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description FROM Orders LEFT JOIN ( SELECT LineItems.Quantity, LineItems.Description, OrderId, ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY (SELECT NULL)) AS RowNum FROM LineItems ) LineItems2 ON LineItems2.OrderId = Orders.OrderID And RowNum = 1 

Às vezes você só precisa testar qual consulta oferece melhor desempenho.

Subconsultas correlacionadas são subconsultas que dependem da consulta externa. É como um loop for no SQL. A subconsulta será executada uma vez para cada linha na consulta externa:

 select * from users join widgets on widgets.id = ( select id from widgets where widgets.user_id = users.id order by created_at desc limit 1 ) 

Outra abordagem usando a expressão de tabela comum:

 with firstOnly as ( select Orders.OrderNumber, LineItems.Quantity, LineItems.Description, ROW_NUMBER() over (partiton by Orders.OrderID order by Orders.OrderID) lp FROM Orders join LineItems on Orders.OrderID = LineItems.OrderID ) select * from firstOnly where lp = 1 

ou, no final talvez você gostaria de mostrar todas as linhas unidas?

versão separada por vírgulas aqui:

  select * from Orders o cross apply ( select CAST((select l.Description + ',' from LineItems l where l.OrderID = s.OrderID for xml path('')) as nvarchar(max)) l ) lines 

EDIT: nevermind, Quassnoi tem uma resposta melhor.

Para SQL2K, algo como isto:

 SELECT Orders.OrderNumber , LineItems.Quantity , LineItems.Description FROM ( SELECT Orders.OrderID , Orders.OrderNumber , FirstLineItemID = ( SELECT TOP 1 LineItemID FROM LineItems WHERE LineItems.OrderID = Orders.OrderID ORDER BY LineItemID -- or whatever else ) FROM Orders ) Orders JOIN LineItems ON LineItems.OrderID = Orders.OrderID AND LineItems.LineItemID = Orders.FirstLineItemID 

Eu resolvo um problema semelhante usando LEFT JOIN e GROUP BY Orders.OrderNumber. Existe uma razão para não fazer isso dessa maneira?

 SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description FROM Orders LEFT JOIN LineItems ON Orders.OrderID = LineItems.OrderID GROUP BY Orders.OrderNumber 

Responderei sua pergunta de resposta com uma resposta em sua própria pergunta:

 Orders LineItems +-------------+ +---------+----------+---------------+ | OrderNumber | | OrderID | Quantity | Description | +-------------+ +---------+----------+---------------+ | 22586 | | 22586 | 17 | Trunion | +-------------+ | 22586 | 3 | Girdle Spring | +---------+----------+---------------+ 

Juntando os dois juntos no OrderNumber dá:

 OrderNumber Quantity Description ----------- -------- ------------- 22586 17 Trunion 22586 3 Girdle Spring 2 row(s) affected 

Onde queríamos que retornasse apenas uma linha:

 OrderNumber Quantity Description ----------- -------- ------------- 22586 17 Trunion 1 row(s) affected 

É por isso que uso GROUP BY Orders.OrderNumber, que retorna apenas uma linha por OrderNumber.

Tentei a cruz, funciona bem, mas demora um pouco mais. Colunas de linha ajustadas para ter um grupo max e adicionado que manteve a velocidade e caiu o registro extra.

Aqui está a consulta ajustada:

 SELECT Orders.OrderNumber, max(LineItems.Quantity), max(LineItems.Description) FROM Orders INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID Group by Orders.OrderNumber 

Minha maneira favorita de executar essa consulta é com uma cláusula inexistente. Eu acredito que esta é a maneira mais eficiente de executar este tipo de consulta:

 select o.OrderNumber, li.Quantity, li.Description from Orders as o inner join LineItems as li on li.OrderID = o.OrderID where not exists ( select 1 from LineItems as li_later where li_later.OrderID = o.OrderID and li_later.LineItemGUID > li.LineItemGUID ) 

Mas eu não testei este método contra outros methods sugeridos aqui.