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?

Does this answer your question? [Limiting a left join to returning one result?](https://stackoverflow.com/questions/11388443/limiting-a-left-join-to-returning-one-result) — Saghachi, Dec 28 '21 at 18:31

Mithilesh Gupta · Answer 1 · 2017-02-06T07:33:05.130

7

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)

edited Feb 06 '17 at 07:33

answered Feb 06 '17 at 07:21

Mithilesh Gupta

2,800
1
17
17

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? – 90abyss Feb 06 '17 at 07:29

Zohar Peled · Answer 2 · 2017-02-06T07:31:01.957

3

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.

edited Feb 06 '17 at 07:31

answered Feb 06 '17 at 07:24

Zohar Peled

79,642
10
69
121

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? – 90abyss Feb 06 '17 at 07:54
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. – Zohar Peled Feb 06 '17 at 07:57

score 2 · Answer 3 · edited Feb 06 '17 at 07:43

2

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

edited Feb 06 '17 at 07:43

jarlh

42,561
8
45
63

answered Feb 06 '17 at 07:34

Nenad J.

331
2
8

score 0 · Answer 4 · answered Feb 06 '17 at 07:23

0

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

answered Feb 06 '17 at 07:23

Paweł Piwowar

174
2
8

4

This should be a comment, not an answer. – Zohar Peled Feb 06 '17 at 07:28

score 0 · Answer 5 · answered Dec 28 '21 at 10:54

0

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)

answered Dec 28 '21 at 10:54

Saghachi

851
11
19

Don't answer these duplicate questions, flag them as duplicates. [answer] [Help] – philipxy Dec 28 '21 at 17:44

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

5 Answers5