Postgresql – Query returns all values instead of the row of the one with the max value

maxpostgresql

I have this database

https://pastebin.com/WWRxApWn

I need the row of the product with the greatest unitprice. I did:

SELECT id, MAX(unitprice)
FROM product
GROUP BY id;

Instead, it returned every row in products. If I do this:

SELECT MAX(unitprice)
FROM product;

It returns the greatest price, as expected.

I looked into this (https://stackoverflow.com/questions/18957372/sql-max-product-price) and this (https://stackoverflow.com/questions/12366390/how-to-select-product-that-have-the-maximum-price-of-each-category) thread but I can't figure it out.

Help?

Best Answer

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

Related Question