SQL: Left join but select only one row with least created date

Question

Let's say I have this table:

Table A:

 id   name    
---   ---  
 1    John   

Table B:

  id     content    Aid      Date
  ---      ---      ---     ----------
  100      abc       1      2017-02-03 11:16:00
  101      xyz       1      2017-02-03 11:50:00

I want to join A and B such that the resulting table has only the one row from B with Date = minimum.

My desired output:

 id   name  content
---   ---   ------
 1    John    abc

My failed attempt at joining is giving me (1, John, xyz) row as well:

SELECT A.[id], A.[name], B.[content]
FROM A
LEFT JOIN B
ON A.id = B.Aid

Later I tried doing a WHERE inside join but I'm not able to construct it:

SELECT A.[id], A.[name]
FROM A
LEFT JOIN B
ON A.id = (SELECT Aid FROM B WHERE Date = (SELECT MIN(Date) FROM B 
WHERE <no idea where I'm going?>)

Can someone please point me in the right direction?

Rotation

Does this answer your question? Limiting a left join to returning one result? – Codger

Answer 1

SELECT A.[id], A.[name],B.[content]
FROM A
LEFT JOIN B
ON A.id = B.Aid
AND B.Date = (Select Min(Date) from B as B2 where B2.Aid=A.id)

Hallie

If multiple values are present in A and B, still your query is giving me only 1 row in the result set. This row is pivoting on the first entry in B with least created date. Shouldn't the subquery be Select Min(Date) from B WHERE xyz? – Rotation

This works out of the box – Scrobiculate

Answer 2

You can use a left join to a derived table:

SELECT a.ID, a.Name, b.Content
FROM a
LEFT JOIN
(
    SELECT aId, content, ROW_NUMBER() OVER (PARTITION BY aId ORDER BY Date) rn
    FROM b        
) b ON(a.Id = b.AId AND b.rn = 1)

The row_number window function will start with 1 for each aId with the minimum date, so you need to add the condition AND b.rn = 1 to the on clause. If you would have added it to the where clause, it would affectivly change your left join to an inner join.

Velar

thanks for your answer! this is working as expected. can you take a look at Mithilesh's answer? I tried running both but there is no difference in the runtime. I'm not sure which one to choose. Can you help? – Rotation

I think you will only see a difference in run time with a large set of records for both tables. As to which one you should choose - well, I would say go with the one you find easier to understand. – Velar

Answer 3

You can use OUTER APPLY.

SELECT a.ID, a.Name, b.Content
FROM a
OUTER APPLY
(
    SELECT  TOP (1) *
    FROM b
    WHERE b.aid = a.id
    ORDER BY Date ASC
) b

Mendacity

Answer 4

why you don't use SELECT TOP 1 with ORDER BY B.Date DESC ?

Filial

This should be a comment, not an answer. – Velar

Answer 5

Easy solution to left join the 1 most/least recent row is using select over ON phrase

SELECT A.ID, A.Name, B.Content
FROM A
LEFT JOIN B
ON A.id = (SELECT MAX(id) FROM B WHERE id = A.id)

Codger

Don't answer these duplicate questions, flag them as duplicates. How to Answer Help center – Revel