SQL Help only max line from a query-CodePudding

I have the following sql and I get 6 results. I only want to display the highest OpportunityStageID row (still need to see all 4 columns that coincide with the largest OpportunityStageID)

SELECT        COH.CPEID
,os.OpportunityStageID
, OS.OpportunityStageName
, COH.CreateTS
FROM            dbo.tblOpportunityStage AS OS LEFT OUTER JOIN                        
                         dbo.tblCPEOpportunityHistory AS COH ON OS.OpportunityStageID = COH.OpportunityStageID
 where cOH.CPEID = 8437372

This is the output I get and I only want the largest row (in this case 6) I have tried MAX and clauses and I cant figure it out.

Photo of what i get ---> [1]: https://i.stack.imgur.com/NEc1S.png

CodePudding user response：

Your join is implicitly INNER, although you wrote it as LEFT OUTER.

SELECT COH.CPEID
,os.OpportunityStageID
,OS.OpportunityStageName
,COH.CreateTS
FROM dbo.tblOpportunityStage AS OS 
inner JOIN dbo.tblCPEOpportunityHistory AS COH 
  ON OS.OpportunityStageID = COH.OpportunityStageID
where cOH.CPEID = 8437372 and 
OS.OpportunityStageID =
(SELECT max(OpportunityStageID)
FROM tblCPEOpportunityHistory coh
where CPEID = 8437372 and exists (select * from tblOpportunityStage os where os.OpportunityStageID = coh.OpportunityStageID));

CodePudding user response：

So I ended up using a table variable and using the Row_NUmber() over clause and I was able to get the data I wanted.