eraelpeha
eraelpeha

Reputation: 419

Retrieving multiple MAX() values from different tables

I want to retrieve multiple MAX() values from different tables in postgresql, like

SELECT MAX(t1.id) AS t1max, MAX(t2.price) AS t2price FROM table1 t1, table2 t2

The output should look like

+-----------------------+
| t1max | t2price | ... |
+-----------------------+
| 100   |   20    | ..  |

just one row. The above version works, but it's very slow while adding more tables (I think due to cross join). Is there a fast way to achieve that?

Upvotes: 0

Views: 84

Answers (2)

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

Join only results, not the whole tables :

select t1.t1max, t2.t2max 
from
     (select max(id) t1max from table1) t1,
     (select max(price) t2max from table2) t2

Upvotes: 2

GarethD
GarethD

Reputation: 69759

You could do this using sub selects:

SELECT  (SELECT MAX(t1.id) FROM table1 t1) AS t1max, 
        (SELECT MAX(t2.price) FROM table2 t2) AS t2price

Example on SQL Fiddle

Upvotes: 1

Related Questions