Query AdventureWorks 2019

You can try something like this:

• Build a CTE (Common Table Expression) to list the orders, details, and products
• Create a row number - based on ROW_NUMBER() function - to find the most expensive product
• Select from that CTE and show only the most expensive product per order

I used this code (your column names aren't always accurate and don't always exist - I tried to guess as best I could. Adapt if needed):

WITH BaseData AS
(
    SELECT
        soh.SalesOrderNumber,
        ProductName = p.Name,
        sod.OrderQty,
        p.ListPrice,
        RowNum = ROW_NUMBER() OVER (PARTITION BY soh.SalesOrderID ORDER BY p.ListPrice DESC)
    FROM 
        Sales.SalesOrderHeader soh 
    INNER JOIN
        Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
    INNER JOIN 
        Production.Product p ON p.ProductID = sod.ProductID
)
SELECT
    *
FROM 
    BaseData
WHERE
    RowNum = 1
ORDER BY
    SalesOrderNumber;

and I get an output something like this (only first few rows):