Query returns all values instead of the row of the one with the max value
The difference in what you linked is the asker wants the max value per a specific field (i.e. aggregate of a GROUP BY), where you rather want the whole row that contains the max value of a specific field (i.e. take the top 1 row ordered by an aggregate).
To achieve what you want you need to use a window function to order the records by the unitprice field and then filter on that the top 1 of that result set. Here's an example query to achieve this:
WITH CTE_Product_Sorted AS -- CTE of the product dataset with a SortId generated from the order of the unitprice field
(
   SELECT *, ROW_NUMBER() OVER (ORDER BY unitprice DESC) AS SortId -- Generates a unique ID for every record, in the order of unitprice, from greatest to least
   FROM product
)

SELECT *
FROM CTE_Product_Sorted
WHERE SortId = 1 -- Filter out every other record except the one with the greatest unitprice

Note when you use the ROW_NUMBER() window function, if there's a tie in the logic of the ORDER BY clause, the ordering between the tied records is non-deterministic (random). Depending on the case, this is ok, or when it's not then a unique field needs to be added to the ORDER BY clause as a fallback (e.g. ORDER BY unitprice DESC, productid ASC results in the product that was created first to win in the case of a tie by unitprice.)