 <p>One is to INNER JOIN Table B onto Table A by ID's. You will have 3 records returned from Table B. If you ORDER those records by the COLX</p> <pre><code>SELECT ,a.ID ,a.Col1 ,a.Col2 ,a.Col3 ,b.COLX FROM TableA AS a INNER JOIN TABLE_B AS b on b.A_ID = a.id ORDER BY b.COLX DESC </code></pre> <p>Then another way is joining a sub query of Table B that also has a sub query that filters Table B records to only the records with the highest RANK. </p> <p>That way you can bring in COLX values from the highest RANK records from Table B that match the records of Table A. </p> <p>I think at least...</p> <pre><code>SELECT a.ID ,a.Col1 ,a.Col2 ,a.Col3 ,b.COLX FROM TableA a INNER JOIN ( SELECT a.A_ID ,a.RANK ,a.COLX FROM TABLE_B a INNER JOIN ( SELECT A_ID, ,MAX(RANK) AS [RANK] -- Highest Rank FROM TABLE_B GROUP BY A_ID ) AS b ON b.A_ID = a.A_ID AND b.RANK = a.RANK ) AS b on b.A_ID = a.id ORDER BY a.ID ASC </code></pre>