CODEWITHSUNDEEP
mysqlmax
AaronDT
AaronDT

Reputation: 4060

How to return SQL max() values in different columns?

I have the following two tables products and prices.

The price table looks as follows:

Price ID|Price
6       |1800
7       |2500   
8       |1800

the product table as follows:

Product_ID  |Price_ID 
1           | 6       
2           | 7       
3           | 8

In oder to know the price of any product I joined the two tables together using

 select * from price
 left join product
    on product.Price_ID = price.Price_ID

which gives me the follwing result, indicating that there are prices in my prices table that are not assigned to any product. 

Price_ID|Price  |Product_ID
6       |1800   |NULL
7       |2500   |NULL   
8       |1800   |8
9       |2100   |9

I want to know two things, what is the maximum price from the price table and what is the maximum price that any product has? How would I acomplish this within one SQL statement?

The desired result should look something like this:

max_price | max_price_of_product|
2500      | 2100                |

I have tried to use two select statements combined with union, however the results appear stacked in one column

Thank you very much!

Upvotes: 0

Views: 39

Answers (3)

Jon Spencer
Jon Spencer

Reputation: 39

I believe you will need to do this through nested SELECT statements that are UNIONed together. I'm sure the code below is not syntactically correct, but hopefully will point you in the right direction.

Select max_price, max_price_of_Product from
    (SELECT Max(Price) As max_price FROM PriceTable
    UNION ALL
    SELECT Max(Price) As max_price_of_product FROM
        (
            select * from price
            left join product
            on product.Price_ID = price.Price_ID
        )
)

Upvotes: 0

Dan M
Dan M

Reputation: 1

select MAX(Price) as max_price from price
 left join product
    on product.Price_ID = price.Price_ID

For the max_price column, I don't have enough information for the max_price_of_product one.

Upvotes: 0

Barmar
Barmar

Reputation: 781096

You can use an IF() expression in the MAX() function to get the max of all the prices that have a matching Product_ID.

SELECT MAX(price) as max_price, MAX(IF(product_id IS NOT NULL, price, null)) AS max_product_price
FROM price
LEFT JOIN product ON price.Price_ID = product.Price_ID

Upvotes: 1

Related Questions