How to Join to first row

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.DescriptionFROM     OrdersJOIN     LineItemsON       LineItems.LineItemGUID =         (         SELECT  TOP 1 LineItemGUID          FROM    LineItems         WHERE   OrderID = Orders.OrderID         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

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

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle

I know this question was answered a while ago, but when dealing with large data sets, nested queries can be costly. Here is a different solution where the nested query will only be ran once, instead of for each row returned.

SELECT   Orders.OrderNumber,  LineItems.Quantity,   LineItems.DescriptionFROM   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

@Quassnoi answer is good, in some cases (especially if the outer table is big), a more efficient query might be with using windowed functions, like this:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.DescriptionFROM    OrdersLEFT 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

Sometimes you just need to test which query gives better performance.