LEFT OUTER JOIN and only return the first match

Question

Imagine the following two tables, named "Users" and "Orders" respectively:

ID  NAME
1   Foo
2   Bar
3   Qux


ID  USER  ITEM  SPEC  TIMESTAMP
1   1     12    4     20150204102314
2   1     13    6     20151102160455
3   3     25    9     20160204213702

What I want to get as the output is:

USER   ITEM  SPEC  TIMESTAMP
1      12    4     20150204102314
2      NULL  NULL  NULL
3      25    9     20160204213702

In other words: do a LEFT OUTER JOIN betweeen Users and Orders, and if you don't find any orders for that user, return null, but if you do find some, only return the first one (the earliest one based on timestamp).

If I use only a LEFT OUTER JOIN, it will return two rows for user 1, I don't want that. I thought of nesting the LEFT OUTER JOIN inside another select that would GROUP BY the other fields and fetch the MIN(TIMESTAMP) but that doesn't work either because I need to have "SPEC" in my group by, and since those two orders have different SPECs, they still both appear.

Any ideas on how to achieve the desired result is appreciated.

Pரதீப் · Accepted Answer · 2016-03-15T18:33:59.853

The best way i can think of is using OUTER APPLY

SELECT *
FROM   Users u
       OUTER apply (SELECT TOP 1 *
                    FROM   Orders o
                    WHERE  u.ID = o.[USER]
                    ORDER  BY TIMESTAMP DESC) ou

Additionally creating a below NON-Clustered Index on ORDERS table will help you to increase the performance of the query

CREATE NONCLUSTERED INDEX IX_ORDERS_USER
  ON ORDERS ([USER], TIMESTAMP)
  INCLUDE ([ITEM], [SPEC]);

score 2 · Answer 2 · answered Mar 15 '16 at 17:11

This should do the trick :

SELECT  Users.ID, Orders2.USER ,  Orders2.ITEM ,  Orders2.SPEC ,  Orders2.TIMESTAMP
FROM    Users
LEFT JOIN 
        (
        SELECT  Orders.ID, Orders.USER ,  Orders.ITEM ,  Orders.SPEC ,  Orders.TIMESTAMP, ROW_NUMBER()
                OVER (PARTITION BY ID  ORDER BY TIMESTAMP DESC) AS RowNum
        FROM    Orders

        ) Orders2 ON Orders2.ID = Users.ID And RowNum = 1

score 1 · Answer 3 · answered Mar 15 '16 at 17:04

Another way is to use a windowing function as a Cte:

with Sorted as
(
  select u.id as User, o.Item, o.Spec, o.Timestamp
         row_number() over (partition by u.Id order by Timestamp) as Row
    from Users u
    left join orders o
      on o.User = u.Id
)
select User, Item, Spec, Timestamp
  from Sorted where Row = 1

score 1 · Answer 4 · edited May 23 '17 at 10:27

1

You will find lots of suggestions in this question. The fact you have a left join is really incidental to what you're trying to do, so those answers should be easy to adapt to your problem. I agree with @MotoGP that for SQLServer an OUTER APPLY is probably the best approach. It is very similar to Postgres's LATERAL JOIN (mentioned in the other link).

edited May 23 '17 at 10:27

Community

1
1

answered Mar 15 '16 at 17:04

Paul A Jungwirth

23,504
14
74
93

LEFT OUTER JOIN and only return the first match

4 Answers4