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!
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;