clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335764 fiddles created (27473 in the last week).

CREATE TABLE A(Id INT NOT NULL, Name VARCHAR(100)) CREATE TABLE B(Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, AId INT) INSERT INTO A (Id, Name) VALUES (1, 'A'), (2, 'B'), (4, 'C'), (8, 'D'), (9, 'E') INSERT INTO B (AId) VALUES (2), (3), (4), (5), (6), (7), (8), (9) -- When trying to pull an A.Id that doesn't exist (like 3, 5, 6, 7) it should pull the previous ID, sometimes there are large gaps SELECT * FROM B OUTER APPLY (SELECT TOP (1) * FROM A WHERE A.Id <= B.AId ORDER BY A.Id DESC) AS A -- Expected: -- Id AId Id Name -- 1 2 2 B -- 2 3 2 B -- 3 4 4 C -- 4 5 4 C -- 5 6 4 C -- 6 7 4 C -- 7 8 8 D -- 8 9 9 E DROP TABLE B DROP TABLE A
Id AId Id Name
1 2 2 B
2 3 2 B
3 4 4 C
4 5 4 C
5 6 4 C
6 7 4 C
7 8 8 D
8 9 9 E
 hidden batch(es)