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?

score 3 · Answer 1 · edited Oct 20 '12 at 00:27

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)

user3680115 · Answer 2 · 2014-06-11T09:14:53.743

This is my solution and it is works. I've already tried it.

WITH PRICE_MAX AS (select model, price
from pc
where price = (select max(price) 
               from pc)
UNION
select model, price
from laptop
where price = (select max(price) 
               from laptop)
UNION
select model, price
from printer
where price = (select max(price) 
               from printer))

select model from PRICE_MAX
where price = (select Max(price) 
               from PRICE_MAX)

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

You need to alias your derived tables: see this post

Edit: this should work to get the models with the max price. (I'm not sure if this is the correct syntax for sql server.)

with max_price(price) as (
  SELECT MAX(price)
       FROM (
          SELECT price
          FROM Pc
          UNION ALL
          SELECT price
          FROM Laptop
          UNION ALL 
          SELECT price
          FROM Printer
       ) as sub1
)

SELECT model, price
FROM (
   SELECT model, price
   FROM Pc
   UNION ALL
   SELECT model, price
   FROM Laptop
   UNION ALL 
   SELECT model, price
   FROM Printer
) as sub2
JOIN max_price m ON m.price = sub2.price

edited May 23 '17 at 12:23

Community

1
1

answered Jul 19 '11 at 20:25

Jacob Eggers

9,062
2
25
43

That solved the syntax issue, but now my query returns every single model number, not just the one with the max price.... – nathpilland Jul 19 '11 at 20:39
Almost, but it says price is an invalid column name. Do I need a table. operator to make price unambiguous? – nathpilland Jul 19 '11 at 20:52
I'm not exactly sure what the with ... as is, as you can see I'm quite the beginner :/ I think I understand it, but sql server still doesn't like it. It says no column name was specified for column 1 of 'max_price' – nathpilland Jul 19 '11 at 21:00
Now it just says max_price is an invalid column name. Would using something else besides UNION make it easier? – nathpilland Jul 19 '11 at 21:15
Ok. I think this one should do it. I even tested it on sql server... :) – Jacob Eggers Jul 19 '11 at 21:27
BINGO. Awesome. I appreciate you taking the time to do that! – nathpilland Jul 19 '11 at 21:31

score 0 · Answer 4 · edited Jan 24 '14 at 18:21

Select b.model from
(Select a.model, Max(a.price) as price from
(Select model, MAX(price) as price from PC group by model
union
Select model, MAX(price) as price from Laptop group by model
union
Select model, MAX(price) as price from Printer group by model)a
Group by a.model)b
where b.price=(Select Max(c.price) from(Select model, MAX(price) as price from PC group by model
union
Select model, MAX(price) as price from Laptop group by model
union
Select model, MAX(price) as price from Printer group by model)c)

score -1 · Answer 5 · edited Jun 26 '13 at 10:30

select model from (
Select model, price from pc
where price = (select max(price) from pc)
union
Select model, price from laptop
where price = (select max(price) from laptop)

union
Select model, price from printer
where price = (select max(price) from printer)
) as G
where price = (
select max(price) from (
Select model, price from pc
where price = (select max(price) from pc)
union
Select model, price from laptop
where price = (select max(price) from laptop)

union
Select model, price from printer
where price = (select max(price) from printer)
) as T
)

Using MAX with multiple tables

5 Answers5