Cómo unirse a la primera fila

Usaré un ejemplo concreto, pero hipotético.

Cada Pedido normalmente tiene solo una línea de pedido :

Pedidos:

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

Artículos de línea:

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

Pero ocasionalmente habrá un pedido con dos líneas de pedido:

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

Normalmente al mostrar los pedidos al usuario:

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

Quiero mostrar el artículo único en el pedido. Pero con este pedido ocasional que contiene dos (o más) artículos, los pedidos aparecerán 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

Lo que realmente quiero es que SQL Server elija solo uno , ya que será lo suficientemente bueno :

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

Si me pongo aventurero, podría mostrarle al usuario puntos suspensivos para indicar que hay más de uno:

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

Entonces la pregunta es cómo

eliminar filas "duplicadas"
unirse solo a una de las filas, para evitar duplicaciones

Primer intento

Mi primer intento ingenuo fue unirme únicamente a las líneas de pedido " 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

Pero eso da el error:

La columna o prefijo 'Pedidos' no
coincide con el nombre de una tabla o el nombre de alias
utilizado en la consulta.

Presumiblemente porque la selección interna no ve la tabla exterior.

Jan 11 '10 23:01 Ian Boyd

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

En SQL Server 2005 y superior, puede simplemente reemplazarlo INNER JOINcon 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

Tenga en cuenta que TOP 1sin ORDER BYno es determinista: esta consulta le proporcionará un artículo de línea por pedido, pero no está definido cuál será.

Varias invocaciones de la consulta pueden generar diferentes líneas de pedido para el mismo pedido, incluso si el subyacente no cambió.

Si desea un orden determinista, debe agregar una ORDER BYcláusula a la consulta más interna.

Ejemplo sqlfiddle

Jan 11 '2010 16:01 Quassnoi

Sé que esta pregunta fue respondida hace un tiempo, pero cuando se trata de grandes conjuntos de datos, las consultas anidadas pueden resultar costosas. Aquí hay una solución diferente en la que la consulta anidada solo se ejecutará una vez, en lugar de hacerlo para cada fila devuelta.

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

Apr 06 '2012 21:04 Justin Fisher

La respuesta de @Quassnoi es buena, en algunos casos (especialmente si la tabla exterior es grande), una consulta más eficiente podría ser el uso de funciones en ventana, como esta:

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

A veces sólo es necesario probar qué consulta ofrece mejor rendimiento.

Mar 03 '2016 16:03 BornToCode

Podrías hacerlo:

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
  )

Esto requiere un índice (o clave principal) activado LineItems.LineItemIDy un índice activado LineItems.OrderIDo será lento.

Jan 11 '2010 16:01 Tomalak