Using MAX with multiple tables

Question

I have a four tables: products, pc, laptop, and printer.

    Products(maker, model, type)
    PC(code, model, speed, hd, cd, price)
    Laptop(code, model, speed, ram, hd, screen, price)
    Printer(code, model, color, type price)

What I need is to find the model number of the product(PC, Laptop, or Printer), that has the highest price. This will not work with a case statement because if two model numbers have the highest price, both need to display, and using a case will select only one then exit the case statement. I would like to do this using the UNION operator, but I'm not sure how to do it. This is what I have so far:

SELECT model FROM 
(SELECT model, MAX(price) FROM 
(SELECT model, price FROM Pc UNION ALL SELECT model, price FROM Laptop UNION ALL 
 SELECT model, price FROM Printer) 
 GROUP BY model)

But this is incorrect syntax and I'm not sure why. Any ideas?

Answer 1

Select datatable.model as price from (
    Select P.model,P.price from PC P where P.price=(select Max(Q.price) from PC Q)
    Union 
    Select P.model,P.price from Laptop P where P.price=(select Max(Q.price) from Laptop Q)
    Union 
    Select P.model,P.price from Printer P where P.price=(select Max(Q.price) from Printer Q)
) as datatable where datatable.price=(
    Select Max(newtable.price) as price from (
        Select P.model,P.price from PC P where P.price=(select Max(Q.price) from PC Q)
        Union 
        Select P.model,P.price from Laptop P where P.price=(select Max(Q.price) from Laptop Q)
        Union 
        Select P.model,P.price from Printer P where P.price=(select Max(Q.price) from Printer Q)) as newtable)