 <p>Here's the answer, a simple sub-select:</p> <p>declare @t1 table (Id int not null primary key) declare @t2 table (Id int not null primary key, Table1Id int not null, SomeValue int not null)</p> <p>INSERT @t1 VALUES (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113)</p> <p>INSERT @t2 VALUES (1,100,1),(5,100,2),(9,100,4),(10,100,5), (2,101,1),(6,101,2),(11,101,4),(13,101,7), (3,102,1),(7,102,2),(12,102,4),(14,102,6), (15,103,1),(17,103,2), (16,104,1),(18,104,2),(19,104,4), (20,105,1),(25,105,2),(27,105,4),(28,105,7), (21,106,1), (22,107,1),(29,107,2), (23,108,1),(30,108,2), (31,109,1), (32,110,1),(36,110,2),(40,110,3), (33,111,1),(37,111,2),(44,111,3), (34,112,1),(38,112,2),(43,112,4), (35,113,1),(41,113,2),(42,113,4)</p> <p>select * from ( select Table1Id, max (SomeValue) AS SomeValue from @t2 group by Table1Id ) t where SomeValue = 4</p>