 <p>using row number windowed function along with a CTE will do this pretty well. For example:</p> <pre><code>;With preResult AS ( SELECT TOP(100) [ID] = c.[ID], [Name] = c.[Name], [Keyword] = [colKeyword].[StringVal], [DateAdded] = [colDateAdded].[DateVal], ROW_NUMBER()OVER(PARTITION BY c.ID ORDER BY [colDateAdded].[DateVal]) rn FROM @cards AS c LEFT JOIN @cardindexes AS [colKeyword] ON [colKeyword].[CardID] = c.ID AND [colKeyword].[IndexType] = 1 LEFT JOIN @cardindexes AS [colDateAdded] ON [colDateAdded].[CardID] = c.ID AND [colDateAdded].[IndexType] = 2 WHERE [colKeyword].[StringVal] LIKE 'p%' AND c.[CardTypeID] = 1 ORDER BY [DateAdded] ) SELECT * from preResult WHERE rn = 1 </code></pre>