Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Feb 6 '12 at 1:58

This question came from our site for professional and enthusiast programmers.

    
The question is ambiguous - roughly, it is ambiguous between the two interpretations in the two answers A and B. Are you looking for the single id of the item with the global maximum price, or are you looking for the (up to) three ids of the items that are most expensive according to each of the three tables. –  Jonathan Leffler Feb 5 '12 at 19:22
    
This strikes me as a logical problem with your DBMS schema. You probably either want to merge all 3 tables into one, or (if you have lots of code that depends on the current arrangement) create a view that merges all 3 tables into one. –  Ken Bloom Feb 5 '12 at 19:58
    
This question is pure SQL programming and belongs on StackOverflow where it was originally asked. Migrating it to DBA simply reduces the number of people who will ever benefit from it,. –  Jonathan Leffler Feb 6 '12 at 3:40
    
@JonathanLeffler: agree in this case, but dba.se isn't just for backup/restore monkeys. See meta.dba.stackexchange.com/questions/564/… and meta.dba.stackexchange.com/questions/495/… –  gbn Feb 6 '12 at 5:11
3  
@JonathanLeffler: "DBA" is for DB admin, BI, advanced SQL, database design etc meta.dba.stackexchange.com/questions/503/… Here, you'll get more eyeballs from database types than on SO. Also see meta.dba.stackexchange.com/questions/535/… –  gbn Feb 6 '12 at 5:38
show 1 more comment

6 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
But if there are more than one products which have max price –  Ashot Feb 5 '12 at 19:22
    
@Ashot: I fixed that with my update, written while you were asking your auxilliary question. When you're dealing with a complex query, build it up piece by piece, as I showed (and as I've shown in many other answers to SQL questions). Trying to do it all at once just leaves the mind boggled, and the query broken, and then you have to rip it apart and test the component pieces to see where it is going wrong, so it would have been easier to build it up from tested components than to do, undo and redo. –  Jonathan Leffler Feb 5 '12 at 19:35
add comment
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.

share|improve this answer
    
+1 for the temporary table, probably not worth to recreate all columns though: create temporary table allthree as SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1) ... –  Bruno Silva Feb 5 '12 at 19:28
    
@Bruno: good idea. Post that as an answer and I'll upvote you. (Actually, once you've gone so far as to create a temporary table with so little data in it, you're probably better off finishing the query on the client side.) –  Ken Bloom Feb 5 '12 at 19:32
    
Probably this is a better solution, Thanks –  Ashot Feb 5 '12 at 19:33
    
@KenBloom Posted my answer. –  Bruno Silva Feb 5 '12 at 19:37
add comment

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);
share|improve this answer
add comment

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.

share|improve this answer
    
You find id of product which has max price in his table. I need ids of products which price are max from all 3 tables –  Ashot Feb 5 '12 at 19:18
2  
Strictly speaking, select by id, max(price) isn't allowed in SQL, however MySQL allows it as an extension -- it will pick whichever id is convenient. –  Ken Bloom Feb 5 '12 at 19:20
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.