How to Join to first row

Question

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

Each Order normally has only one line item:

Orders:

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

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

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:

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:

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:

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:

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:

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.

Ebeneser

Can't you use group by? – Shyamal

I think (and correct me if I'm wrong) group by would require listing all the other columns, excluding the one where you don't want duplicates. Source – Artless

Does this answer your question? Fetch the rows which have the Max value for a column for each distinct value of another column – Cosentino

Answer 1

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       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.Description
FROM    Orders
CROSS 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

Plateau

Excellent, that works; moving TOP 1 from derived table clause to join clause. – Ebeneser

and the "OUTER JOIN" equivalent would be "OUTER APPLY" – Fluoroscopy

How about for LEFT OUTER JOIN? – Bulimia

@AlexanderN: what exactly you want to know about LEFT OUTER JOIN? – Plateau

How do you do this if the join is via a compound key/has multiple columns? – Onshore

I just knocked 1.3 seconds off the execution time of a complex T-SQL query thanks to this (3.7 down to 2.4 seconds). :-D – Foliar

@Lex: INNER JOIN does not generate NULL value for rows missing from the joined query, LEFT JOIN does. – Plateau

Is there a way to do this without a top 1 in the inner select? Unfortunately OpenEdge being so state of the art you get [DataDirect][OpenEdge JDBC Driver][OpenEdge] TOP clause used in unsupported context. (13694) – Onshore

Is there an advantage to using Cross Apply? – Perdure

@JeffDavis: it will save you one seek per row if OrderId is the leading column in the primary key. – Plateau

@Plateau - Could you please explain your last comment about cross apply being more efficient if order id is primary key? Why is that and why it doesn't happen with join? – Lonne

@BornToCode: cross apply would retrieve the whole record and return it right away. Join would retrieve the row, take line item guid from it, self join the table on the guid (that's an additional seek) and return the record from the joined table. – Plateau

Unfortunately not supported on Sybase IQ – Immovable

Importantly - the second solution - Cross Apply / Outer Apply will work with SQLCE which cannot use Scalar subqueries in joins as per the top query – Lallans

CROSS APPLY instead INNER JOIN and OUTER APPLY instead LEFT JOIN (the same as LEFT OUTER JOIN). – Cullie

Keep in mind that TOP, without ORDER BY is non-deterministic. You are not guaranteed to get the row you think you will. – Enteritis

Alternatively, use MIN() instead of TOP 1. I find that I am usually interested in the first Id. – Hybridize

CROSS APPLY ( in most cases ) will be time consuming... User OUTER APPLY instead. – Aq

If you're on PostgreSQL, instead of TOP 1, just add limit 1 after the inner WHERE clause – Apograph

This wouldn't work with MySQL – Digester

As @Hybridize already suggested, use MIN(Column) or MAX(Column) rather than using ORDER BY. Scanning through and picking the Max or Min will usually be faster than sorting in many cases. – Pseudohermaphrodite

Answer 2

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

Maronite

This is also much faster if your 'LineItemId' column is not indexed properly. Compared to the accepted answer. – Cottage

But how would you do this if Max is not usable as you need to order by a column different to the one you want to return? – Lichen

you can order the derived table whichever way you want and use TOP 1 in SQL Server or LIMIT 1 in MySQL – Wares

Found this to be much faster on larger data sets – Owain

Could you please elaborate? As far as only syntax is concerned, your answer is as nested as Quassnoi's: exactly one subquery. You cannot just imply that one will run "for each row returned" and the other will not just because the syntax seems so. You have to include a plan. – Sidestep

@GeorgeMenoutis My understanding is that a query like bar = (SELECT ...) is akin to running the sub-query for each row (example: https://mcmap.net/q/53016/-join-vs-sub-query). On an execution plan this is noticeable by looking at the number of executions. I suggest comparing the different methods to determine which works best in your situation. In my case, the method used in my answer was significantly faster than the sub-query and cross apply methods in the accepted answer when dealing with large datasets and the LineItems needed to be ordered (deterministic). – Maronite

Answer 3

@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 which query gives better performance.

Lonne

This is the only answer I found that does a real "Left" join, meaning it does not add any more lines then is in the "Left" table. You just need to put in subquery and add "where RowNum is not null" – Sporty

Agreed this is the best solution. This solution also does not require you to have a unique ID in the table you're joining to, and is much faster than the top voted answer. You can also add criteria for which row you prefer to return, rather than just taking a random row, by using an ORDER BY clause in the subquery. – Deck

This is a good solution. Please note: when using for your own situation, be very careful how you PARTION BY (usually you do probably want some ID column there) and ORDER BY (which could be done by most anything, depending on which row you want to keep, e.g. DateCreated desc would be one choice for some tables, but it would depend on a lot of things) – Betrothal

What does imply the (SELECT NULL) in the OVER clause? – Spies

Thanks. This worked for me as well as the accepted answer with left join was giving all the rows in the second table which I didn't wanted. – Alyse

Answer 4

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.

Jermyn

This does not work if an Orders has no LineItems. The sub-expression then evaluates LineItems.LineItemID = null and removes the left entity orders completely from the result. – Graniteware

That's also the effect of the inner join, so... yeah. – Jermyn

Solution that can be adapted for LEFT OUTER JOIN: https://mcmap.net/q/54543/-select-first-record-in-a-one-to-many-relation-using-left-join – Graniteware

@Graniteware Yes, but the OP used an inner join himself, so I don't understand your objection. – Jermyn

Answer 5

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

Gabriella

Doesn't the "INNER JOIN LineItems" cause multiple rows to be returned when an order has more than one line item? – Suffix

The DISTINCT should take care of this – Gabriella

Answer 6

,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

Morningglory

Answer 7

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
)

Hayseed

Answer 8

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.

Emend

Answer 9

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

Newberry

Answer 10

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

Aldosterone

But having max separately on two columns means the quantity might not be related to the description. If the order was 2 Widgets and 10 Gadgets, the query would return 10 Widgets. – Quetzal