CODEWITHSUNDEEP
sqlsql-server
vitasya
vitasya

Reputation: 355

select related to max field

I have 3 tables: laptop, pc, printer with model and price fields.

I need to get max price among all products in these all tables.

I did union to get max price & model field for each table:

SELECT model,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 X

As a result of this, I received

model | price

 1233  | 980.0000
 1276  | 400.0000
 1288  | 400.0000
 1750  | 1200.0000

And now I'm stuck getting only model (in this case it will be '1750') with max price.

Upvotes: 1

Views: 49

Answers (2)

SqlZim
SqlZim

Reputation: 38033

using union all and top 1

select top 1 model, price 
from (
    select top 1 model, price 
    from pc 
    order by price desc

    union all

    select top 1 model, price 
    from laptop 
    order by price desc

    union all

    select top 1 model, price 
    from printer
    order by price desc
  ) as u
order by price desc

I do not know if this will create a different execution plan than Gordon's answer... I'm off to go find out.

This answer produces the same query execution plan as Gordon's answer in SQL Server 2012, and as Gordon's is more concise I would go with that one.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270181

How about using order by and top?

SELECT TOP 1 model, price
FROM  (SELECT model, price 
       FROM pc 
       UNION ALL
       SELECT model, price 
       FROM laptop 
       UNION ALL
       SELECT model, price 
       FROM printer
      ) t
ORDER BY price DESC;

Note: If you have indexes, this is probably not the most efficient way to get the information you want. You would need to provide more information about the design of the tables and their data if performance is an issue.

Upvotes: 1

Related Questions