title: SQL Most Recent Register FROM Second Table by Id
body: <p>I have 2 tables (Opportunity and Stage). I need to get each opportunity with the most recent stage by StageTypeId.</p>
  
  <p>Opportunity: Id, etc
  Stage: Id, CreatedOn, OpportunityId, StageTypeId.</p>
  
  <p>Let's suppose I have "opportunity1" and "opportunity2" each one with many Stages added.</p>
  
  <p>By passing the StageTypeId I need to get the opportunity which has this StageTypeId as most recent.</p>
  
  <p>I'm trying the following query but it´s replicating the same Stage for all the Opportunities.</p>
  
  <p>It seems that it's ignoring this line:  "AND {Stage}.[OpportunityId] = ID"    </p>
  
  <pre><code>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]
  </code></pre>
  
  <p>Thank you</p>