Get the whole ROW which has the MAX value for a column with join

I have exhausted my wit with this problem, so I bring the challenge to you:

--CREATING DATA:

DECLARE @CONTACTS TABLE (ID INT, NickName CHAR(10))
INSERT INTO @CONTACTS VALUES (1359,'John Smith')
INSERT INTO @CONTACTS VALUES (1360,'Ana Keller')
INSERT INTO @CONTACTS VALUES (1361,'Joshua Doe')

DECLARE @ENTERPRISES TABLE (ID INT, CON_ID INT, ASSOCIATION DATETIME)
INSERT INTO @ENTERPRISES VALUES (123,1359,Convert(datetime, '2000-10-09', 120))
INSERT INTO @ENTERPRISES VALUES (123,1360,Convert(datetime, '2000-02-21', 120))
INSERT INTO @ENTERPRISES VALUES (123,1361,Convert(datetime, '2000-04-09', 120))

--QUERYING THE DATA:
SELECT * FROM @CONTACTS C
JOIN @ENTERPRISES E ON C.ID = E.CON_ID

--THE RESULT SET OF MY DREAMS:
    1359    John Smith    123    1359    2000-10-09

What I want is an entire row of the query before, but only the one that has the maximum ASSOCIATION date for e开发者_C百科ach Enterprise ID. This is only an example for the sake of clarity, my real query is much more complex, with much more columns and trillions of rows. I have tried

SELECT *,max(FOUNDING) OVER (PARTITION BY E.ID) FROM @CONTACTS C
JOIN @ENTERPRISES E ON C.ID = E.CON_ID

But it still brings all three rows. Other approaches were worse and led to the parser asking me to GROUP BY every column. I appreciate your help.

WITH cteMaxAssociation AS (
    SELECT ID, CON_ID, ASSOCIATION,
           ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ASSOCIATION DESC) AS RowNum
        FROM @ENTERPRISES
)
SELECT C.ID, C.NickName, ma.ID, ma.CON_ID, ma.ASSOCIATION
    FROM @CONTACTS C
        INNER JOIN cteMaxAssociation ma
            ON C.ID = ma.CON_ID
                AND ma.RowNum = 1

SELECT *
FROM  @CONTACTS C
JOIN @ENTERPRISES E ON C.ID = E.CON_ID 
   (SELECT max(association) as maxdate, CON_ID , ID, ASSOCIATION 
     FROM Entpresises
     GROUP BY CON_ID ) maxresults
WHERE E.ID = maxresults.ID
AND E.ASSOCIATION = maxresults.ASSOCIATION ;