How can I make null values be considered as MAX in SQL?

Question

To be able to explain the situation, let's say I have a table

Product price
Cola    2
Cola    null
Fanta    1
Fanta    2
Sprite   2
Sprite null

I need to write a query that would return the maximum price per product and if the price is null, would consider it the maximum. So for this table it should return Cola null, Fanta 2, Sprite null.

I really appreciate your help! Thank you in advance.

Answer 1

Standard SQL allows you to specify where NULL values should be sorted using the expression NULLS FIRST or NULLS LAST in the ORDER BY statement. This can be combined with a window function to get the desired behaviour:

select product, price
from (
   select product,
          price,
          row_number() over (partition by product order by price desc nulls first) as rn
   from products
) t
where rn = 1
order by product, price desc nulls first
;

With Postgres it is usually faster to use distinct on for this kind of queries:

select distinct on (product) product, price
from products
order by product, price nulls first

Answer 2

select product, case when sum(case when price is null then 1 else 0 end) > 0
                     then null
                     else max(price)
                end as price
from your_table
group by product

Answer 3

I decided to take the following approach. Of course, you will need to decide what "type" your price column is and make the max of the column equal to the max value within the DBMS you are using.

SELECT product, NULLIF(MAX(COALESCE(price, 2147483647)),2147483647) as max_price
FROM product_pricing
GROUP BY product