SQL Latest Record FROM segnda table by Id

Asked

Viewed 49 times

0

I have two tables (Opportunity and Stage). I need to pick for each Opportunity the table row Stage where the StageTypeId is the same as input parameter.

Table Opportunity

Id, etc

Table Stage

Id, Createdon, Opportunityid, Stagetypeid.

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

Passing the StageTypeId I need to pick up the Opportunities who have this Id as the most recent in the table Stage.

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

Looks like you’re ignoring that 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.

sql

  • one max maybe help here, but honestly it’s hard to imagine the tables, it would be more practical to post the structure (relevant fields) and an example of data to be clear

  • @Denis, you’ve solved your problem?

  • @Joãomartins yes!

2 answers

1


After a while I decided to run the first query and then retrieve the table data Stage item by item in a Loop.

1

Hello @Denis, I know it’s been a long time since the question, but I think it’s valid to leave it quoted here:

This approach of doing a new query with each loop usually brings problems when we are talking about a large number of records.

What you needed was a Join taking into consideration only 1 record of the auxiliary table.

http://www.sqlfiddle.com/#! 9/d471e2/2/0

    SELECT opportunity.id,
      opportunity.name,
      contacts.name,
      stage.stageTypeId
  FROM opportunity
  JOIN contacts ON contacts.id = opportunity.contact_id
  JOIN (SELECT stageTypeId,MAX(createdOn),opportunityId FROM stage GROUP BY opportunityId) as stage
   ON stage.opportunityId = opportunity.id 

  WHERE stage.stageTypeId=2

http://www.sqlfiddle.com/#! 9/d471e2/2/0

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.