Select row with Distinct max(column value)

Question

[ExactReplica].[FilteredOpportunityProduct] Table

Opportunityid      baseamount
1                  500
1                  500
2                  600
2                  700

[ExactReplica].FilteredOpportunity Table

Opportunityid   name
1                ABC
2                CDF

I want to take the maximum baseamount; however, am facing issue when there exists duplicate of the baseamount, how can I take only one record

My Query

  select
     MaxAmount.[baseamount] ,
     c.name


 FROM [ExactReplica].FilteredOpportunity c

 Left JOIN
        (
            SELECT  opportunityid,
                    MAX((baseamount)) baseamount
            FROM    [ExactReplica].[FilteredOpportunityProduct] 
            GROUP BY   opportunityid

        ) MaxAmount ON c.opportunityid = MaxAmount.opportunityid
    inner JOIN
        [ExactReplica].[FilteredOpportunityProduct]  p ON   MaxAmount.opportunityid = p.opportunityid
                    AND MaxAmount.baseamount = p.baseamount

Answer 1

Try this:

select max(baseamount) baseamount,a.name
from
(select
     baseamount, ROW_NUMBER() over (partition by p.opportunityid,baseamount order by p.baseamount desc) rn,
     c.name

 FROM FilteredOpportunity c
    inner JOIN
        [FilteredOpportunityProduct]  p ON   c.opportunityid = p.opportunityid) a
        where rn=1
        group by a.name

OUTPUT:

baseamount  name
500         ABC
700         CDF

Answer 2

Can you try below query for expected result, I have executed the below scripts :

For Table Creation :

CREATE TABLE FILTEREDOPPORTUNITYPRODUCT ( OPPORTUNITYID INT NULL, BASEAMOUNT VARCHAR(24) NULL )

CREATE TABLE FILTEREDOPPORTUNITY ( OPPORTUNITYID INT NULL, NAME VARCHAR(24) NULL )

Insertion:

INSERT INTO FILTEREDOPPORTUNITYPRODUCT (OPPORTUNITYID,BASEAMOUNT) VALUES (1,500),(1,500),(2,600),(2,700)

INSERT INTO FILTEREDOPPORTUNITY (OPPORTUNITYID,NAME) VALUES (1,'ABC'),(2,'CDF')

Selection:

SELECT A.OPPORTUNITYID,B.NAME,MAX(BASEAMOUNT) AS BASEAMOUNT FROM FILTEREDOPPORTUNITYPRODUCT AS A
JOIN FILTEREDOPPORTUNITY AS B
ON A.OPPORTUNITYID = B.OPPORTUNITYID GROUP BY A.OPPORTUNITYID,B.NAME