Maximum value from multiple tables

Question

Consider the following situation: we have T1, T2, & T3 tables in which saved id and price of products. Now we need to find the id of products which have maximum price of all 3 tables. I have this solution:

select id
from T1
where price in(
   select max(price)
   from( 
      select max(price) as price
      from T1

      union

      select max(price) as price
      from T2

      union

      select max(price) as price
      from T3
   ) temp
)   

union 

select id
from T2
where price in(
   select max(price)
   from( 
      select max(price) as price
      from T1

      union

      select max(price) as price
      from T2

      union

      select max(price) as price
      from T3
   ) temp
)   

union

....    same for T3 table.

Is it possiple to optimize this query?

Answer 1

I'm assuming that you mean you want the id of the item which is the most expensive based on the data from all three tables. For each table, you need the id and the price of the item(s) with the maximum price in that table. For one table, that is:

SELECT id, price FROM Tn WHERE price = (SELECT MAX(price) FROM Tn)

So, there's a sub-query:

SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
UNION
SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
UNION
SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)

And, you need to find one of the id values with the maximum price from that sub-query:

SELECT id
  FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
        UNION
        SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
        UNION
        SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
       ) AS M
 ORDER BY price DESC
 LIMIT 1

Note that if there are two equally expensive items listed in any one of the tables, the UNION query will return more than 3 rows. If there are two or more equally expensive items, the query with the LIMIT chooses one, and you can't predict which. If you need to see all the id values of equally expensive items that cost the most, then you need to play a similar game using nested queries. This blows my mind, but should work:

SELECT id, price
  FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
        UNION
        SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
        UNION
        SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
       ) AS M
 WHERE price =
       (SELECT MAX(Price)
          FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
                UNION
                SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
                UNION
                SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
               ) AS M2
       );

This would be easier using a WITH clause:

WITH MaxPrices AS
     (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
      UNION
      SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
      UNION
      SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
     )
SELECT id, price
  FROM M
 WHERE price = (SELECT MAX(Price) FROM M);

Not every DBMS supports the WITH clause like this, and I believe MySQL is one of the DBMS in the category that does not include such support.

Answer 2

create temporary table allthree as
select * from t1
union
select * from t2
union
select * from t3;

select id, max(price) from allthree;

If you need to get all of the IDs that have the same price in the event of a tie (or you need to be more strict for other reasons), use the following

select id from allthree where price=(select max(price) from allthree);

In some circumstances, it may help to create an index on the price column after creating the temporary table.

Answer 3

Optimized version:

create temporary table allthree as
SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
UNION ALL
SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
UNION ALL
SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3);

select id from allthree where price=(select max(price) from allthree);

Answer 4

I'm not sure if I understood your question but shouldn't this be enough:

select id, max(price) as price
from T1
union all
select id, max(price) as price
from T2
union all
select id, max(price) as price
from T3

This will get the maximum values for each table.

Answer 5

I hope my syntax works since I am not a MySql person usually.

SELECT *
FROM (
   SELECT 'T1' AS TableName, * FROM Table1
   UNION ALL SELECT 'T2', * FROM Table2
   UNION ALL SELECT 'T3', * FROM Table3
) AS X
WHERE
   price = (
      SELECT Max(price)
      FROM (
         SELECT price FROM Table1
         UNION ALL SELECT price FROM Table2
         UNION ALL SELECT price FROM Table3
      ) AS X
   )

If MySql has windowing functions or CTEs or CROSS APPLY or other techniques I am not aware of for helping to select entire rows that fulfill some aggregated condition, my query could be improved or simplified.

Answer 6

CTE's to the rescue!

This query also retrieves an indication of the table where the max was found.

WITH three AS (
        SELECT 1::INTEGER AS num, id, zdate FROM table1
        UNION ALL
        SELECT 2::INTEGER AS num, id, zdate FROM table2
        UNION ALL
        SELECT 3::INTEGER AS num, id, zdate FROM table3
        )
SELECT * FROM three tt
WHERE NOT EXISTS (
        SELECT * FROM three nx
        WHERE nx.zdate > tt.zdate
        );

The bad news is: Postgres's query optimiser does not "see" the indexes (on zdate) inside the unions, and does not pull them up into the main query. The query plan consists of nested sequential scans.

 Nested Loop Anti Join  (cost=297.82..315505.01 rows=3055 width=16) (actual time=633.593..633.594 rows=1 loops=1)
   Join Filter: (nx.zdate > tt.zdate)
   CTE three
     ->  Result  (cost=0.00..297.82 rows=4582 width=16) (actual time=0.013..2.623 rows=4582 loops=1)
           ->  Append  (cost=0.00..297.82 rows=4582 width=16) (actual time=0.011..2.143 rows=4582 loops=1)
                 ->  Seq Scan on table1  (cost=0.00..99.19 rows=1519 width=16) (actual time=0.011..0.884 rows=1519 loops=1)
                 ->  Seq Scan on table2  (cost=0.00..99.12 rows=1512 width=16) (actual time=0.006..0.449 rows=1512 loops=1)
                 ->  Seq Scan on table3  (cost=0.00..99.51 rows=1551 width=16) (actual time=0.003..0.450 rows=1551 loops=1)
   ->  CTE Scan on three tt  (cost=0.00..91.64 rows=4582 width=16) (actual time=0.021..0.630 rows=4582 loops=1)
   ->  CTE Scan on three nx  (cost=0.00..91.64 rows=4582 width=8) (actual time=0.000..0.069 rows=757 loops=4582)
 Total runtime: 633.820 ms