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.

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](https://stackoverflow.com/questions/13999817) — jyn, Jun 01 '18 at 12:25
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Jan 18 '23 at 02:21

score 1470 · Accepted Answer · edited Nov 20 '20 at 17:11

1470

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

edited Nov 20 '20 at 17:11

Alexander Schmidt

5,631
4
39
79

answered Jan 11 '10 at 16:48

Quassnoi

413,100
91
616
614

3

Excellent, that works; moving TOP 1 from derived table clause to join clause. – Ian Boyd Jan 11 '10 at 16:54
130

and the "OUTER JOIN" equivalent would be "OUTER APPLY" – Alex from Jitbit Dec 22 '11 at 10:41
10

How about for LEFT OUTER JOIN? – Alex Nolasco Jan 31 '12 at 23:11
@AlexanderN: what exactly you want to know about `LEFT OUTER JOIN`? – Quassnoi Feb 01 '12 at 09:53
8

How do you do this if the join is via a compound key/has multiple columns? – Brett Ryan Sep 12 '12 at 07:20
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 – Sunday Ironfoot Dec 11 '12 at 11:19
@Lex: `INNER JOIN` does not generate `NULL` value for rows missing from the joined query, `LEFT JOIN` does. – Quassnoi Nov 29 '13 at 18:25
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)` – Brett Ryan Jun 26 '14 at 03:50
1

Is there an advantage to using Cross Apply? – Jeff Davis Sep 17 '15 at 14:27
2

@JeffDavis: it will save you one seek per row if `OrderId` is the leading column in the primary key. – Quassnoi Sep 17 '15 at 14:31
@Quassnoi - 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? – BornToCode Dec 06 '15 at 11:28
1

@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. – Quassnoi Dec 06 '15 at 12:30
Unfortunately not supported on Sybase IQ – Dirk Horsten Apr 07 '16 at 08:16
1

Importantly - the second solution - Cross Apply / Outer Apply will work with SQLCE which cannot use Scalar subqueries in joins as per the top query – statler Jan 16 '17 at 10:07
12

`CROSS APPLY` instead `INNER JOIN` and `OUTER APPLY` instead `LEFT JOIN` (the same as `LEFT OUTER JOIN`). – hastrb Mar 15 '18 at 12:04
2

Keep in mind that TOP, without ORDER BY is non-deterministic. You are not guaranteed to get the row you think you will. – Paul Wehland Sep 21 '18 at 10:59
2

Alternatively, use MIN() instead of TOP 1. I find that I am usually interested in the first Id. – 9Rune5 Dec 06 '18 at 13:05
CROSS APPLY ( in most cases ) will be time consuming... User OUTER APPLY instead. – Rohan K Jul 24 '19 at 08:27
If you're on PostgreSQL, instead of `TOP 1`, just add `limit 1` after the inner `WHERE` clause – Guilherme Barboza Feb 23 '22 at 17:25

Justin Fisher · Answer 2 · 2012-04-06T22:34:38.273

144

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

edited Apr 06 '12 at 22:34

answered Apr 06 '12 at 21:25

Justin Fisher

1,813
1
13
12

3

This is also much faster if _your_ 'LineItemId' column is not indexed properly. Compared to the accepted answer. – GER Jan 20 '15 at 16:43
5

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? – NickG Apr 24 '15 at 16:04
2

you can order the derived table whichever way you want and use TOP 1 in SQL Server or LIMIT 1 in MySQL – stifin Jun 09 '15 at 10:39
1

Found this to be much faster on larger data sets – DotNetDublin Jan 11 '21 at 16:27
1

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. – George Menoutis Jan 21 '21 at 18:15
@GeorgeMenoutis My understanding is that a query like `bar = (SELECT ...)` is akin to running the sub-query for each row (example: https://stackoverflow.com/questions/2577174/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). – Justin Fisher Jan 22 '21 at 21:35

score 58 · Answer 3 · edited Apr 13 '17 at 12:42

58

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

edited Apr 13 '17 at 12:42

Community

1
1

answered Mar 03 '16 at 16:14

BornToCode

9,495
9
66
83

4

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" – user890332 May 10 '19 at 17:55
2

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. – Geoff Griswald Oct 03 '19 at 14:06
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) – JosephDoggie Mar 23 '20 at 20:30
What does imply the `(SELECT NULL)` in the OVER clause? – Simone Sep 08 '21 at 09:30
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. – Varun Sharma Nov 15 '22 at 19:40

score 30 · Answer 4 · answered Jan 11 '10 at 16:50

30

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.

answered Jan 11 '10 at 16:50

Tomalak

332,285
67
532
628

2

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. – leo Jul 02 '15 at 09:53
8

That's also the effect of the inner join, so... yeah. – Tomalak Jul 02 '15 at 09:58
1

Solution that can be adapted for LEFT OUTER JOIN: http://stackoverflow.com/a/20576200/510583 – leo Jul 02 '15 at 10:38
4

@leo Yes, but the OP used an inner join himself, so I don't understand your objection. – Tomalak Jul 02 '15 at 10:56

score 27 · Answer 5 · answered Nov 28 '18 at 11:55

27

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

answered Nov 28 '18 at 11:55

P. Olesen

286
3
4

Doesn't the "INNER JOIN LineItems" cause multiple rows to be returned when an order has more than one line item? – David Pierson Jul 27 '22 at 07:15
1

The DISTINCT should take care of this – P. Olesen Sep 16 '22 at 13:32

avb · Answer 6 · 2017-05-10T10:18:11.177

,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

score 12 · Answer 7 · edited Sep 17 '16 at 10:38

12

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
)

edited Sep 17 '16 at 10:38

Racil Hilan

24,690
13
50
55

answered Sep 17 '16 at 10:19

Abdullah Yousuf

403
5
7

score 5 · Answer 8 · answered May 09 '17 at 18:12

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.

Peter Radocchia · Answer 9 · 2010-01-11T17:23:56.997

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

score 3 · Answer 10 · edited Feb 14 '13 at 22:07

3

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

edited Feb 14 '13 at 22:07

Krease

15,805
8
54
86

answered Feb 14 '13 at 21:47

ernst

71
1

15

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. – Brianorca Aug 04 '15 at 23:52

How to Join to first row

First attempt

10 Answers10

Linked

Related