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
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