How to get the soonest date in relation to another date field

Question

Say I have a date field in one table (table a):

+---------+------------+
| item_id | Date       |
+---------+------------+
| 12333   | 10/12/2020 |
+---------+------------+
| 45678   | 10/12/2020 |
+---------+------------+

Then I have another table with another date, and it joins to the table above as so (they join on the primary key of table b):

+-------------+------------+-----------+------------+
| primary_key | date2      | item_id   | Date       |
| (table b)   | (table b)  | (table a) | (table a)  |
+-------------+------------+-----------+------------+
| 45318       | 10/10/2020 | 12333     | 10/12/2020 |
+-------------+------------+-----------+------------+
| 45318       | 10/13/2020 | 12333     | 10/12/2020 |
+-------------+------------+-----------+------------+
| 45318       | 10/24/2020 | 12333     | 10/12/2020 |
+-------------+------------+-----------+------------+
| 75394       | 10/20/2020 | 45678     | 10/12/2020 |
+-------------+------------+-----------+------------+

You see the last column is from table a. I want to get table b's "date2" column to give me the soonest date after 10/12/2020, and remove the rest.

So for the example of 45318, I want to keep the second line only (the one that is 10/13/2020) since that is the soonest date after 10/12/2020.

If this doesn't make sense, let me know and I will fix it!

Answer 1

One method is apply:

select a.*, b.*. -- or whatever columns you want
from a outer apply
     (select top (1) b.*
      from b
      where b.item_id = a.item_id and
            b.date2 >= '2020-10-12'
      order by b.date2 asc
     ) b;