Query from union of joins

Question

I have been doing sql exercises from sql-ex.ru site and stumbled into one that I can't make correct.

Here it is:

Find the model number of the product (PC, laptop, or printer) with the highest price.

Result set: model.

Database schema is explained here. I will just put its diagram here:

I came up with this solution:

with model_price(model,price) as (
select model,price 
from PC

union

select model,price 
from Laptop

union

select model,price 
from Printer
)

select model
from model_price
where price >= all(select price from model_price)

The system outputs the following result:

Your query produced correct result set on main database, but it failed test on second, checking database.
* Wrong number of records (less by 2)

The system provides the answer for the main database. But I don't know what the checking database is and can't determine what is wrong by executing subqueries separately and looking where I make mistake.

So I'm asking here in the hope that you can notice what I do wrong. This task is of level 2 (quite simple), so it should be something trivial that I'm missing.

_{About sql engine: Now we use Microsoft SQL Server 2012 on the rating stages, and MySQL 5.5.11, PostgreSQL 9.0, and Oracle Database 11g on the learn stage in addition. So I don't know which engine exactly they use to asses this exercise.}

Answer 1

The solution was to use subquery with max function instead of comparing with all:

with model_price(model,price) as (
select model,price 
from PC

union

select model,price 
from Laptop

union

select model,price 
from Printer
)

select model
from model_price
where price = (select max(price) from model_price)

No need for distinct keyword as union returns unique rows.

Thanks to tombom who helped me find this.

The solution with all doesn't work, as price column may have NULL values, and comparing to NULL produces false, which make the whole result for all false, while using max just searches for he maximum price ignoring NULLs. This I learned by asking this question.

Answer 2

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 A
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 B
)

Answer 3

SELECT model, price FROM (
  (SELECT model, price FROM pc ORDER BY price DESC LIMIT 1)
    /* your database may use a TOP 1 syntax for the LIMIT queries */
   UNION ALL
  (SELECT model, price FROM laptop ORDER BY price DESC LIMIT 1)
   UNION ALL
  (SELECT model, price FROM printer ORDER BY price DESC LIMIT 1)
 ) AS subquery_name_required
 ORDER BY price DESC LIMIT 1;

If the price field is indexed (which it should be for a query like this), the ORDER/LIMIT (or equivalent TOP) will run very fast as the DB plucks the highest price from the table.

Note: In a database that supports table inheritance (or can be kludged to support something like it with discriminator columns), refactoring the common columns into a single table has a lot to say for it! Imagine if you start selling scaners, monitors, SSDs, etc.—what a nightmare!

[EDIT to show ties]

SELECT model, price FROM (
  SELECT model, price FROM 
    (SELECT model, price, rank() OVER (ORDER BY price DESC) AS r FROM pc) AS s1
    WHERE r=1
    /* your database may not yet support windowing queries;
      in that case you probably have to compare to MAX,
      which may mean an extra pass through every table. */
   UNION ALL
  SELECT model, price FROM 
    (SELECT model, price, rank() OVER (ORDER BY price DESC) AS r FROM laptop) AS s2
    WHERE r=1
   UNION ALL
  SELECT model, price FROM 
    (SELECT model, price, rank() OVER (ORDER BY price DESC) AS r FROM printer) AS s3
    WHERE r=1
 ) AS subquery_name_required
 ORDER BY price DESC LIMIT 1;

I don't have enough experience with this sort of windowing to know if an index on price will be scanned just as far as necessary. I hope so. Conceptually that shouldn't be more difficult than the LIMIT 1, just have to recognize that it's a trickier but similar query.

Answer 4

WITH tab1 as
(
        /* laptop */
        (
            SELECT 1 model, 2000 price FROM DUAL UNION ALL
            SELECT 2 model, 2250 price FROM DUAL UNION ALL
            SELECT 3 model, 2500 price FROM DUAL UNION ALL
            SELECT 4 model, 2550 price FROM DUAL           -- <---- the highest value
        )
        UNION ALL
        /* printer */
        (
            SELECT 5 model, 500  price FROM DUAL UNION ALL
            SELECT 6 model, 750  price FROM DUAL UNION ALL
            SELECT 7 model, 1000 price FROM DUAL UNION ALL
            SELECT 8 model, 1250 price FROM DUAL
        )
        UNION ALL
        /* pc */
        (
            SELECT 9  model, 1000 price FROM DUAL UNION ALL
            SELECT 10 model, 1250 price FROM DUAL UNION ALL
            SELECT 11 model, 1500 price FROM DUAL UNION ALL
            SELECT 12 model, 1750 price FROM DUAL
        )
)
SELECT  model
FROM    tab1
WHERE   price = (SELECT MAX(price) FROM tab1);

Answer 5

Please have a try with the following. It's ANSI-Standard SQL, so you don't have to worry, which RDBMS is used.

SELECT
model 
FROM
(
SELECT
model, price
FROM
pc
UNION ALL
SELECT
model, price
FROM
printer
UNION ALL
SELECT
model, price
FROM
laptop
) sq 
WHERE price = (SELECT MAX(price) FROM (
  SELECT
  model, price
  FROM
  pc
  UNION ALL
  SELECT
  model, price
  FROM
  printer
  UNION ALL
  SELECT
  model, price
  FROM
  laptop
) sq2
)

Answer 6

I think i caught the problem: you use union with models and price: if you have two different products (code) with the same price (the max) and model you end giving only one row (union instead union all eliminates the duplicates). The result set is the correct, yes, but in the validation they say that you gave only one row instead of two