How to Join to first row

I'll use a concrete, but hypothetical, example.

Each **Order** normally has only one **line item**:

**Orders:**

<!-- language: lang-none -->

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

**LineItems:**

<!-- language: lang-none -->

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

But occasionally there will be an order with two line items:

<!-- language: lang-none -->

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

Normally when showing the orders to the user:

<!-- language: lang-sql-->

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

I want to show the single item on the order. But with this occasional order containing two (or more) items, the orders would *appear* be **duplicated**:

<!-- language: lang-none -->

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

What I really want is to have SQL Server *just pick one*, as it will be *good enough*:

<!-- language: lang-none -->

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

If I get adventurous, I might show the user, an ellipsis to indicate that there's more than one:

<!-- language: lang-none -->

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

So the question is how to either

- eliminate "duplicate" rows
- only join to one of the rows, to avoid duplication


## First attempt ##
My first naive attempt was to only join to the "**TOP 1**" line items:

<!-- language: lang-sql -->

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

But that gives the error:

> The column or prefix 'Orders' does not
> match with a table name or alias name
> used in the query.

Presumably because the inner select doesn't see the outer table.

You could do:

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
)

This requires an index (or primary key) on `LineItems.LineItemID` and an index on `LineItems.OrderID` or it will be slow.

EDIT: nevermind, Quassnoi has a better answer.

For SQL2K, something like this:

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

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.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

Tried the cross, works nicely, but takes slightly longer. Adjusted line columns to have max and added group which kept speed and dropped the extra record.

Here's the adjusted query:

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

@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.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

Sometimes you just [need to test](

[To see links please register here]

) which query gives better performance.

Correlated sub queries are sub queries that depend on the outer query. It’s like a for loop in SQL. The sub-query will run once for each row in the outer query:

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
)

My favorite way to run this query is with a not exists clause. I believe this is the most efficient way to run this sort of query:

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
)

But I have not tested this method against other methods suggested here.

,Another aproach using common table expression:

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
or, in the end maybe you would like to show all rows joined?

comma separated version here:

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

From SQL Server 2012 and onwards I think this will do the trick:

SELECT DISTINCT
o.OrderNumber ,
FIRST_VALUE(li.Quantity) OVER ( PARTITION BY o.OrderNumber ORDER BY li.Description ) AS Quantity ,
FIRST_VALUE(li.Description) OVER ( PARTITION BY o.OrderNumber ORDER BY li.Description ) AS Description
FROM Orders AS o
INNER JOIN LineItems AS li ON o.OrderID = li.OrderID