CODEWITHSUNDEEP
sqlmax
EsotericRider
EsotericRider

Reputation: 127

Finding the highest price from three different tables

I have to find the highest price from three different tables but I do not know how to get the max of P now.

PC(model,speed,ram,hd,price)

Laptop(model,speed,ram,hd,screen,price)

Printer(model,color,type,price)

SELECT model
FROM ((SELECT MAX(price)
      FROM PC)
      UNION
      (SELECT MAX(price)
      FROM Laptop)
      UNION
      (SELECT MAX(price)
      FROM Printer)) AS P
WHERE MAX(P);

Could i just add MAX in front? like

SELECT model
FROM MAX((SELECT MAX(price)
     FROM PC)
     UNION
     (SELECT MAX(price)
     FROM Laptop)
     UNION
     (SELECT MAX(price)
     FROM Printer));

Upvotes: 1

Views: 1370

Answers (5)

Not a real meerkat
Not a real meerkat

Reputation: 5739

You can, if you use MAX in a subquery:

SELECT model FROM (
    (SELECT model, price FROM PC)
    union
    (SELECT model, price FROM Laptop)
    union
    (SELECT model, price FROM Printer)
) as AllPrices
WHERE price = (
    select max(val) from (
        (SELECT max(price) as val FROM PC)
        union
        (SELECT max(price) as val FROM Laptop)
        union
        (SELECT max(price) as val FROM Printer)
    ) as MaxPrices
)
LIMIT 1

This is bigger, but is actually faster than simply selecting the whole tables and ordering, because it gives better chances for optimization by the database engine(It doesn't need to sort all records, just the ones that matter). Testing on Postgresql, it ran at half the time on a very large database.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270493

If you want the model, use union all and order by. You don't specify the database, so here is the ANSI standard solution:

SELECT model, price
FROM ((SELECT model, price FROM PC)
      UNION ALL
      (SELECT model, price FROM Laptop)
      UNION ALL
      (SELECT model, price FROM printer)
     ) p
ORDER BY price desc
FETCH FIRST 1 ROW ONLY;

The only part of this that would really differ among databases is the FETCH FIRST 1 ROW ONLY. This might be limit, top, or some other construct.

Upvotes: 3

Koshinae
Koshinae

Reputation: 2320

Note: You haven't specified a vendor, and I'll use Oracle documentations for my answer (however it might be portable).

Do you only need the highest price? Then the subquery will suffice for now, but you use the aggregate function incorrectly:

SELECT
  MAX(PRICE) AS HIGHEST_FROM_ALL_TABLES
FROM
  (
    SELECT MAX(PRICE) AS PRICE FROM PC
    UNION
    SELECT MAX(PRICE) AS PRICE FROM LAPTOP
    UNION
    SELECT MAX(PRICE) AS PRICE FROM PRINTER
  )

However, if you need to have the model also, you'll need a slightly different approach.

SELECT
  MAX(PRICE) KEEP(DENSE_RANK LAST ORDER BY PRICE) AS HIGHEST_PRICE,
  MAX(MODEL) KEEP(DENSE_RANK LAST ORDER BY PRICE) AS THE_MODEL
FROM
  (
    SELECT PRICE, MODEL FROM PC
    UNION ALL
    SELECT PRICE, MODEL FROM LAPTOP
    UNION ALL
    SELECT PRICE, MODEL FROM PRINTER
  )

Here, we:

  1. Unite the three tables that luckily have the same column layout.
  2. Use the LAST analytic function without a partition to get only one row in the end. (mssql translation: here)

The result is the globally max price and it's related model. If you have multiple rows with the same price, it still returns only one, the last that the order walks through.

The best approach would be to get the ID of the desired row, and have this analytic selection as a subquery. Then fetch the only row that has this ID.

Upvotes: 1

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

If you want to get only 1 record with highest price you should use TOP 1. Something like that:

SELECT TOP 1 model
FROM ((SELECT MAX(price)
       FROM PC)
      UNION
      (SELECT MAX(price)
       FROM Laptop)
      UNION
      (SELECT MAX(price)
       FROM Printer)) AS P
WHERE MAX(P)
ORDER BY P DESC;

Upvotes: 1

George Sovetov
George Sovetov

Reputation: 5238

First of all, you should to find max(price) for each table. In this case, query will calculate all using indices (provided you have one for price).

UPD: Further, you better to normalize tables and extract price (and other common fields) to separate table. That will allow you to use this table for price manipulations and treat your entities without care about their concrete types.

Upvotes: 1

Related Questions