Query to pull data from column based off max value of second column

I have a table that has [Order], [Yield], [Scrap], [OpAc] columns. I need to pull the yield based on the max value of [OpAc].

Expected Results

The query that I have tried is

select [Order], [Yield], MAX([OpAc]) as Max_OpAc
from SCRAP
GROUP BY [Order], [Yield]
order by [order]

This produces

I’ve tried setting up some CTE queries to break it down into separate functions but I keep getting caught at this step.

WITH CTE1 AS(
    SELECT ROW_NUMBER() OVER(PARTITION BY [Order] ORDER BY [Order],[OpAc]) AS RN , * 
    FROM SAP_SCRAP
),

This proved to be redundant due to the fact that the [OpAc] field is sequential for each step.

Thanks in advance for any help

>Solution :

You almost got it!

WITH Orders_By_OpAc_Desc AS (
    SELECT
        [Order],
        [Yield].
        ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY OpAc DESC) AS [rn],
    FROM
        SCRAP
)

SELECT [Order],
       [Yield]
FROM
    Orders_By_OpAc_Desc
WHERE
    rn = 1

The trick here is ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY OpAc DESC) AS [rn]. It might be confusing to understand in SQL, but when expressed in words it’s a bit clearer.

This statement takes each group of rows with the same Order value (PARTITION BY [Order]), orders each group by OpAc in descending order so that the higher OpAc values end up "on top" of the group (ORDER BY OpAc DESC), and numbers each row in the group "top" to "bottom", starting with 1 (ROW_NUMBER()).

Meaning, each row with this number set to 1 has the highest OpAc value for the OrderId.

Wrap that into a CTE and then select just the rows with this number (rn) set to 1. Voi-la.