SQL Help only max line from a query

Trip Burton 0
Viewed 28

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

max sql
2 Answers

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));
Cetin Basoz0

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.

Trip Burton0
Related