SQL Most recent record FROM second table by Id

I have two tables ( Opportunity and Stage ). I need to get for every Opportunity the table row Stage where StageTypeId is equal to input parameter .

Table Opportunity

  

Id, etc.

Table Stage

  

Id, CreatedOn, OpportunityId, StageTypeId.

Suppose I have opportunities "opportunity1" and "opportunity2" each with multiple Stages registered.

Passing StageTypeId I need to get the Opportunities that has this Id as the most recent one in the Stage table.

I'm trying the query below but is replicating the Stage that exists for a Opportunity to all other Opportunities .

It looks like you're ignoring this line: AND {Stage}.[OpportunityId] = ID

SELECT {Opportunity}.[Id] ID,
       {Opportunity}.[Name],
       {Opportunity}.[PotentialAmount],
       {Contact}.[FirstName], 
       {Contact}.[LastName],
       (SELECT * FROM 
            (
                SELECT {Stage}.[StageTypeId]
                  FROM {Stage}         
                 WHERE {Stage}.[StageTypeId] = @StageTypeId  
                   AND {Stage}.[OpportunityId] = ID       
                 ORDER BY {Stage}.[CreatedOn] DESC
            ) 
        WHERE ROWNUM = 1) AS StageTypeId     
 FROM {Opportunity}
 LEFT JOIN {Contact} 
   ON {Opportunity}.[ContactId] = {Contact}.[Id]

Thank you.