CODEWITHSUNDEEP
sqlsimplifysimplification
ivkremer
ivkremer

Reputation: 1275

SQL query simplification

select model from (
    select price, model from pc where price = (select max(price) from pc)
    union
    select price, model from laptop where price = (select max(price) from laptop)
    union
    select price, model from printer where price = (select max(price) from printer)
) t1 where price = (select max(price) from (
    select price, model from pc where price = (select max(price) from pc)
    union
    select price, model from laptop where price = (select max(price) from laptop)
    union
    select price, model from printer where price = (select max(price) from printer)
) t2 )

I'm very new to SQL so my question is very simple, but I would like to sort out one point. Am I right that this query can not be simplified to something like this?

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

And if it can not be, is it a bad thing that we run two same subqueries?

Upvotes: 0

Views: 830

Answers (3)

Gonzalo.-
Gonzalo.-

Reputation: 12672

select * from (
    select price, model from pc where price = (select max(price) from pc)
    union
    select price, model from laptop where price = (select max(price) from laptop)
    union
    select price, model from printer where price = (select max(price) from printer)
) order by Price desc limit 1

Since you got 3 values to compare, from different tables, with no relation, you have to do a Union, and then compare them

This is a way, and you don't need to calculate the price again.

Upvotes: 0

Matzi
Matzi

Reputation: 13925

Try something like this:

select model, price
from (
    select price, model from pc order by price desc limit 1
    union
    select price, model from laptop order by price desc limit 1
    union
    select price, model from printer order by price desc limit 1
) t1 
order by price desc
limit 1

However I would suggest you to review your database structure, this seems like you created multiple table for the same stuff (items) based on type. You could keep all this in one table, differenciated by only a content of a type column.

Without limit:

select t1.model, t1.price
from 
(select max(price) p
 from
    select max(price) p from pc
    union
    select max(price) p from laptop
    union
    select max(price) p from printer
) max_price
JOIN (
    select price, model from pc
    union
    select price, model from laptop
    union
    select price, model from printer
) t1 ON price >= max_price.p

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

I still say to go with one table, which is best practice design. (Not duplicating identical tables unnecessarily.)

CREATE TABLE unified_table (
  product_type,
  price,
  model
)

Doing so enables this query...

SELECT
  *
FROM
  unified_table
WHERE
  price = (SELECT MAX(price) FROM unified_table)

But, if you can't, or won't, trust the optimiser to deal with the consequences of the UNIONs...

SELECT
  *
FROM
(
  SELECT * FROM pc
  UNION ALL
  SELECT * FROM laptop
  UNION ALL
  SELECT * FROM printer
) t1
WHERE
  price = (SELECT MAX(price) FROM (SELECT price FROM pc
                                   UNION ALL
                                   SELECT price FROM laptop
                                   UNION ALL
                                   SELECT price FROM printer
                                  ) t2
          )

The optimiser will understand how to optimise this so as to remove redundant searches.


EDIT:

As a compromise, you can make a unified view, and query that...

CREATE VIEW unified_table AS
  SELECT 'pc'      AS type, * FROM pc
  UNION ALL
  SELECT 'laptop'  AS type, * FROM laptop
  UNION ALL
  SELECT 'printer' AS type, * FROM printer

Upvotes: 1

Related Questions